CODEWITHSUNDEEP
mysqlduplicates
Mark
Mark

Reputation: 43

How to sum after removing duplicates

I have one question in mysql.

myTable right below.

Location     Name           Stock
A-site       Keyboard       2
A-site       Mouse          1
B-site       Keyboard       2
D-site       Monitor        1
A-site       Keyboard       -1
C-site       Desktop        1

I would like to express it like this:

Name         A-site   B-site   C-site   D-site    Total
Keyboard     1        2        0        0         3
Mouse        1        0        0        0         1
Monitor      0        0        0        1         1
Desktop      0        0        1        0         1

deduplication and summation are needed.

Please help me on how to make the query in mysql.

Thanks.

Later added: "Location" is a variable. So, in order to use sum by comparison like SUM(IF..), I need to know the deduplicated list in advance. The point of this question is this.

Upvotes: 0

Views: 54

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

This is pivot problem. It can be solved by next query:

select 
    Name,
    sum(if(Location = 'A-site', Stock, 0)) as `A-site`,
    sum(if(Location = 'B-site', Stock, 0)) as `B-site`,
    sum(if(Location = 'C-site', Stock, 0)) as `C-site`,
    sum(if(Location = 'D-site', Stock, 0)) as `D-site`,
    sum(Stock) as total
from stock
group by Name
order by Name
;

SQL fiddle

Result:

+==========+========+========+========+========+=======+
| Name     | A-site | B-site | C-site | D-site | total |
+==========+========+========+========+========+=======+
| Desktop  | 0      | 0      | 1      | 0      | 1     |
+----------+--------+--------+--------+--------+-------+
| Keyboard | 1      | 2      | 0      | 0      | 3     |
+----------+--------+--------+--------+--------+-------+
| Monitor  | 0      | 0      | 0      | 1      | 1     |
+----------+--------+--------+--------+--------+-------+
| Mouse    | 1      | 0      | 0      | 0      | 1     |
+----------+--------+--------+--------+--------+-------+

Upvotes: 1

Related Questions