Quick Tip 001 – Multiplication without a cursor

Download Source Files: Here

In this post I will illustrate how you can multiply rows in a table together without using a cursor.

The SQL for this is not particularly difficult but the results are really useful.  The following sample illustrates creating a table, adding 3 values to that table (using the new SQL Server 2008 row constructor insert method).  And then using the simple algorithm EXP(SUM(LOG(<<column>>))) to multiply all three values together; 2 x 3 X 4 =  24.

USE tempdb

GO



IF OBJECT_ID('Numbers') IS NOT NULL

DROP TABLE Numbers;



CREATE TABLE Numbers

(

   Num INT

);



INSERT INTO Numbers

VALUES (2),(3),(4);



SELECT

   EXP(SUM(LOG([Num]))) AS result

FROM  
   Numbers;