I hope I am explaining this correctly.
I have 2 tables, with
first table (table1
)
x
+------------+------+-------+-------+
| 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.
Advertisement
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