Geek Answers Handbook

Remove Dups then Sum

I am working with an inconvenient MySQL data table. I have a group of records that I take out, but the records are duplicates. I need to summarize the individual values ​​accordingly. Here is the situation ...

Given the data

   identifier category value
       1 a 40
       1 a 20
       1 b 80
       1 c 40
       1 a 80
       2 a 20
       2 b 40

I want to pull out the next

   identifier category
       1,200
       2 60

In this case, I want to exclude duplicates, as defined by the composite identifier category key +, using MAX in the "value" field. This leaves me with the following:

   identifier category value
       1 a 80
       1 b 80
       1 c 40
       2 a 20
       2 b 40

SUM .

, , , , SQL. , (SUM). .

+4
1

Max identifier + category

select identifier,category,max(value)
from table1 
group by identifier,category;

SUM

select identifier,sum(value) as value
from 
(
select identifier,category,max(value) as value
from table1 
group by identifier,category
) a
group by identifier
+2

More articles:


All Articles