I'm new to MySQL so please be patient :)
I have a table where I would like to retrieve data based on date and do Sum
Here is my table
client datescanned problem title
abc 2019-02-02 12345a High xyz
abc 2019-02-02 12345b High xyz
abc 2019-02-02 12345c High xyz
abc 2019-02-02 12345d Medium xyz
abc 2019-02-09 12345e High xyz
abc 2019-02-09 12345f High xyz
abc 2019-02-09 12345g Low xyz
abc 2019-02-09 12345h Low xyz
abc 2019-02-09 12345j Low xyz
abc 2019-02-16 12345x High xyz
abc 2019-02-16 12345s High xyz
abc 2019-02-16 12345w High xyz
abc 2019-02-16 12345bs Medium xyz
My desired output would be
client datescanned problem High Medium Low
abc 2019-02-02 12345x 3 1 0
abc 2019-02-09 12345s 2 0 3
abc 2019-02-16 12345w 3 1 0
This is my code
select client,datescanned, problem, severity,
count(case when severity = 'High' then 1 end) as High,
count(case when severity = 'Medium' then 1 end) as Medium,
count(case when severity = 'Low' then 1 end) as Low
from ssstest where client = "myuser"
group by client,datescanned, problem
This will fetch all the numbers correctly, however I want to "sum" up High, Medium, Low per date.
Now I get..
client datescanned problem severity High Medium Low
abc 2019-02-02 12345a High 1 0 0
abc 2019-02-02 12345b High 1 0 0
abc 2019-02-02 12345c High 1 0 0
abc 2019-02-02 12345d Medium 0 1 0
abc 2019-02-09 12345e High 1 0 0
abc 2019-02-09 12345f High 1 0 0
abc 2019-02-09 12345g Low 0 0 1
abc 2019-02-09 12345h Low 0 0 1
abc 2019-02-09 12345j Low 0 0 1
abc 2019-02-16 12345x High 1 0 0
abc 2019-02-16 12345s High 1 0 0
abc 2019-02-16 12345w High 1 0 0
abc 2019-02-16 12345bs Medium 0 1 0
So basically, I just want to add up High, Medium, Low, group the same date together as one.
client datescanned problem High Medium Low
abc 2019-02-02 12345x 3 1 0
abc 2019-02-09 12345s 2 0 3
abc 2019-02-16 12345w 3 1 0
Thanks again for your help!