Vahn Vahn - 6 months ago 11
MySQL Question

How to SUM a column based on another column?

I have a table like this:

ID Item Total
P-001 ATK001 1500
P-002 ATK001 1750
P-002 ATK002 1250
P-003 ATK001 175
P-003 ATK002 350
P-003 ATK003 400
P-003 CTK001 750
P-003 CTK002 750
P-005 ATK004 500
P-005 ATK117 475
P-005 CTK009 1200
P-005 ATK001 400
P-005 ATK002 125


I'd like to sum the 'Total' Column based on 'Item' column. I've created some mysql query and the output was like this :

P-001 ATK001 3825
P-002 ATK002 1725
P-003 ATK003 400
P-005 ATK004 500
P-005 ATK117 475
P-003 CTK001 750
P-003 CTK002 750
P-005 CTK009 1200


My desired output is :

ID Item Total
P-001 ATK001 3825
P-002 ATK001 3825
P-002 ATK002 1725
P-003 ATK001 3825
P-003 ATK002 1725
P-003 ATK003 400
P-003 CTK001 750
P-003 CTK002 750
P-005 ATK004 500
P-005 ATK117 475
P-005 CTK009 1200
P-005 ATK001 3825
P-005 ATK002 1725


Thank you for your help :)

Answer

Try this;)

SQL Fiddle

MySQL 5.6 Schema:

CREATE TABLE table1
    (`ID` varchar(5), `Item` varchar(6), `Total` int)
;

INSERT INTO table1
    (`ID`, `Item`, `Total`)
VALUES
    ('P-001', 'ATK001', 1500),
    ('P-002', 'ATK001', 1750),
    ('P-002', 'ATK002', 1250),
    ('P-003', 'ATK001', 175),
    ('P-003', 'ATK002', 350),
    ('P-003', 'ATK003', 400),
    ('P-003', 'CTK001', 750),
    ('P-003', 'CTK002', 750),
    ('P-005', 'ATK004', 500),
    ('P-005', 'ATK117', 475),
    ('P-005', 'CTK009', 1200),
    ('P-005', 'ATK001', 400),
    ('P-005', 'ATK002', 125)
;

Query 1:

select t1.ID, t1.Item, t2.Total
from table1 t1
inner join(
    select Item, sum(Total) as Total from table1 group by Item
) t2 on t1.Item = t2.Item

Results:

|    ID |   Item | Total |
|-------|--------|-------|
| P-001 | ATK001 |  3825 |
| P-002 | ATK001 |  3825 |
| P-002 | ATK002 |  1725 |
| P-003 | ATK001 |  3825 |
| P-003 | ATK002 |  1725 |
| P-003 | ATK003 |   400 |
| P-003 | CTK001 |   750 |
| P-003 | CTK002 |   750 |
| P-005 | ATK004 |   500 |
| P-005 | ATK117 |   475 |
| P-005 | CTK009 |  1200 |
| P-005 | ATK001 |  3825 |
| P-005 | ATK002 |  1725 |