SUMPRODUCT (Equivelant to Excel)

**BLaaaaaaaaaarche** · BLaaaaaaaaaarche, July 15th, 2009 09:33 PM

Is there a SUMPRODUCT function equivelant in SQL? SUMPRODUCT multiplies two arrays and sums the totals:

s_shares, s_price_per_share
50, 24.00
40, 18.50
30, 12.00

The SUMPRODUCT of that would be:

(50 * 24.00) + (40 * 18.50) + (30 * 12) = 1,200 + 740 + 360 = 2,300

My table is called tblStocks_details and field names are above in bold. Any ideas how to do this in SQL? I want to GROUP BY s_id, as I need to display a summary of each s_id in a recordset.

**mehere** · mehere, July 15th, 2009 09:41 PM

can you try something like this:

Code:

SELECT id, sum(s_shares) as No_of_shares, sum(s_shares * s_price_per_share) as sum_product FROM tbl_TableName GROUP BY id

**BLaaaaaaaaaarche** · BLaaaaaaaaaarche, July 15th, 2009 09:44 PM

If I wanted to get the "average" of the shares purchased, could I do something such as:

Code:

SELECT id, sum(s_shares) as No_of_shares, (sum(s_shares * s_price_per_share) / No_of_shares) as sum_product FROM tbl_TableName GROUP BY id

Is that valid SQL?

**mehere** · mehere, July 15th, 2009 09:49 PM

sql has an AVG function ... SQL AVG() Function

**BLaaaaaaaaaarche** · BLaaaaaaaaaarche, July 15th, 2009 09:51 PM

Originally Posted by mehere

sql has an AVG function ... SQL AVG() Function

Yes, but its not truly an average. For instance, in my example, the SUMPRODUCT value is 2,300. You need to divide that by the number of shares, which is 120. Your average cost per share would be 2,300 / 120, or $19.17. Whereas the AVG() function would return 2,300 / 3, or $766.67.

Far from the correct value!

(It is much like the weighted average of something, if you have had any experience there)

**mehere** · mehere, July 15th, 2009 09:53 PM

gotcha ... but then you need to do it this way:

Code:

sum(s_shares * s_price_per_share) / sum(s_shares) AS sum_product FROM tbl_TableName GROUP BY id

**BLaaaaaaaaaarche** · BLaaaaaaaaaarche, July 15th, 2009 09:55 PM

Originally Posted by mehere

gotcha ... but then you need to do it this way:

Code:

sum(s_shares * s_price_per_share) / sum(s_shares) AS sum_product FROM tbl_TableName GROUP BY id

Perfect. As usual, thanks for your help.

**mehere** · mehere, July 15th, 2009 10:23 PM

no problem ... glad it worked for you.

Thread: SUMPRODUCT (Equivelant to Excel)

LinkBack

Thread Tools

Display

SUMPRODUCT (Equivelant to Excel)

Similar Threads

If formula help in excel

Excel Convertor

Excel and Email

From excel to database/web app

Excel question

Bookmarks

Bookmarks

Posting Permissions