1

I'm trying to display the sum of my severity levels.

My Table

client    dateadded     problem        level
 abc      2019-02-02    12345a         1
 abc      2019-02-02    12345b         1
 abc      2019-02-02    12345c         2
 abc      2019-02-02    12345d         5
 abc      2019-02-09    12345e         3
 abc      2019-02-09    12345f         3
 abc      2019-02-09    12345g         4
 abc      2019-02-09    12345h         10
 abc      2019-02-09    12345j         8
 abc      2019-02-16    12345x         7
 abc      2019-02-16    12345s         9
 abc      2019-02-16    12345w         4
 abc      2019-02-16    12345bs        5

This is my code

select client, dateadded, 
count(case when level= '1,2,3' then 1 end) as Moderate,
count(case when level= '4,5,6,7' then 1 end) as Severe,
count(case when level= '8,9,10' then 1 end) as Critical          
from table1 where client = 'abc'
group by client, dateadded

I tried

count(case when level= '1' and '2' and '3' then 1 end) as Moderate,   

My desired output

dateadded      Moderate    severe     critical
2019-02-02        3           1           0
2019-02-09        2           1           2
2019-02-16        0           3           2

Thanks! Nathalie

Siva
  • 1,481
  • 1
  • 18
  • 29
enkiki
  • 389
  • 1
  • 3
  • 10

2 Answers2

1

Can you try with IN condition in case and group by dateadded

select client, dateadded, 
count(case when level IN (1,2,3) then 1 end) as Moderate,
count(case when level IN (4,5,6,7) then 1 end) as Severe,
count(case when level IN (8,9,10) then 1 end) as Critical          
from table1 where client = 'abc'
group by dateadded, client
Siva
  • 1,481
  • 1
  • 18
  • 29
0

CASE WHEN level IN(1,2,3) THEN 1 END ... was the way to go.. thanks all!

enkiki
  • 389
  • 1
  • 3
  • 10