SUM PRODUCTS Possible in SQL?

gtsideveloper · #1 (**permalink**) 08-01-02, 15:39

Hi,

Let's say I have 2 tables, A an B and I Inner Join them on a common ID field. Each table has a Quantity Field. For all records in (A Join B) I want to multiply A.Quantity * B.Quantity and Sum All of the Results. This is similar to the SUMPRODUCT Function in Microsoft Excel. Is this possible in SQL?? If so, how??

Thanks

r937 · #2 (**permalink**) 08-01-02, 19:51

dunno if the excel function is the same as this, but here's what you want in sql --

Code:

select sum(A.quantity * B.quantity) as SumAxB
  from A inner join B on A.ID = B.ID

of course, that's the sum for the entire table

if you want the sum for each ID, it's --

Code:

select A.id
     , sum(A.quantity * B.quantity) as SumAxB
  from A inner join B on A.ID = B.ID
group
    by A.ID

rudy
http://rudy.ca/

Internet Services

Web Development

Digital Marketing

Consumer Tech