How To Find Duplicate, Count and Sum Values from different columns in MySQL?

Question

I have my table

My Table

I want to get duplicates from the Name and Status column, count them, and sum values from the Sum column. I want to look like this:

Result

I am new to SQL so that it may be an easy answer, but I can't seem to find a solution.

This is how far I got, but I can't seem to get the count and sum without errors.

SELECT name, COUNT(*) AS recovered 
FROM complaints 
WHERE status = "Recovered" 
GROUP BY name 
HAVING COUNT(name) > 0

myQuery

You should add a proper data sample and the expected result as tabular text and not as image — ScaisEdge, May 11 '20 at 18:18
For Pivoting in MariaDB, see See https://stackoverflow.com/a/56670844/1766831 — Rick James, Nov 01 '21 at 02:28

score 2 · Accepted Answer · answered May 11 '20 at 18:19

You can do conditional aggregation:

select 
    name,
    sum(status = 'Recovered') recovered,
    sum(status = 'Unrecovered') unrecovered,
    sum(case when status = 'Recovered' then `sum` end) total_recovered_value,
    sum(case when status = 'Unrecovered' then `sum` end) total_unrecovered_value
from mytable
group by name
order by name

Side note: sum is a language keyword, hence not a good choice for a column name.

How To Find Duplicate, Count and Sum Values from different columns in MySQL?

1 Answers1