SQL SUM Group by – based on ‘group’ from another table

I hope I am explaining this correctly.

I have 2 tables, with

first table (table1)

+------------+------+-------+-------+
|    Date    | Item | Block | Total |
+------------+------+-------+-------+
| 2017-01    | a1   | B1    |  10.0 |
| 2017-01    | a2   | B1    |  20.0 |
| 2017-01    | a3   | B2    |  30.0 |
| 2017-02    | a1   | B1    |  40.0 |
| 2017-02    | a2   | B1    |  50.0 |
| 2017-02    | a3   | B2    |  60.0 |
+------------+------+-------+-------+

second table (table2)

+------------+------+
| Item Group | Item |
+------------+------+
| IG1        | a1   |
| IG1        | a2   |
| IG2        | a2   |
| IG2        | a3   |
+------------+------+

*Note that, one item group has multiple items. 
The items may appear several time in different item groups.

Now, I need to sum the total (table1), based on Item Group (table2), Date and Block, in the end, final table:

+---------+------------+-------+-------+
|  Date   | Item Group | Block | Total |
+---------+------------+-------+-------+
| 2017-01 | IG1        | B1    |  30.0 |
| 2017-01 | IG2        | B1    |  20.0 |
| 2017-01 | IG1        | B2    |   0.0 |
| 2017-01 | IG2        | B2    |  30.0 |
+---------+------------+-------+-------+

How to achieve this with SQL query?

EDIT:

OK. It seems that this is an easy one. Shame on me. I didn’t know the join and Group By can be applied that way. SQL is really awesome. That saves tons of coding.

Answer

A join and a simple group by should work for you in this case:

select t1.Date, t2.ItemGroup, t1.Block, sum(t1.Total) Total
from table1 t1 join table2 t2 on t1.Item = t2.Item
group by t1.Date, t2.ItemGroup, t1.Block

Advertisement

Answer