CODEWITHSUNDEEP
mysqlsql
ted
ted

Reputation: 4975

Sql SUM over products of grouped elements

I have the following data structure:

Table 1(groups):

ID  | Group
=============
1   | Sample
2   | Data

Table 2(items):

ID  | GroupID | Cost | Amount
==============================
 1  |       1 |   1  | 12
 2  |       1 |   7  | 15
 3  |       1 |   3  |  8
 4  |       2 |   2  | 12

And would like the following (query) results:

groups.ID | groups.Name | total
        1 | Sample      | 141
        2 | Data        |  24

total is the sum over the products of cost and amount of all items in the group i.e. for group 1: 1*12+7*15+3*8=141

Im guessing I have to something with 

SELECT g.ID, g.Group, SUM(Products) 
FROM groups AS g, items AS i 
WHERE g.ID=i.GroupID 
GROUP BY i.GroupID

But don't know what exactly. Doing iit in clientsoftware with loops is no problem, but I am curious (and certain) that this can be done in (my)Sql

Upvotes: 1

Views: 598

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

SELECT g.ID as ID, g.Group as Name, SUM(i.Cost * i.Amount) as total
FROM groups g
INNER JOIN items i ON i.GroupID = g.ID
GROUP BY g.Group, g.ID

Having a field named "Group" is quite a bad idea in SQL (reserved keyword)

Upvotes: 2

Related Questions