1

I'm trying to count the number of occurences based severity level (1-5) on distinct dates. Note I have another table but severity levels are words (High, Medium and Low...not 1 to 5).

Example of DB:

DATE          LEVEL    COUNT
----          -----    -----
05/11/2018    3        14
05/11/2018    5        11
05/11/2018    5        11
05/12/2018    3        14
05/12/2018    2        14
05/13/2018    2        11
05/13/2018    1        12

Expected output

 Date        1    2   3   4   5
 ---------   --   --  --  --  --
 05/11/2018  0    0   14  0   22
 05/12/2018  0    14  14  0   0
 05/13/2018  12   11  0   0   0

Expected output 2

 Level        05/11/2018   05/12/2018  05/13/2018
 ---------    ----------   ----------  ----------
 1               0             0           12       
 2               0             14          11
 3               14            14          0
 4               0             0           0
 5               22            0           0

I tried 

SELECT CONCAT(DAY(`DATE`) ,MONTH(`DATE`) , YEAR(`DATE`)) AS DDMMYYYY , 
 COUNT(DISTINCT LEVEL) as NumCount
FROM  `myDatabase` 
GROUP BY CONCAT(DAY(`DATE`),MONTH(`DATE`), YEAR(`DATE`) )

but I'm getting the number of different counts..

Any guidance would be appreciated! Thx!

enkiki
  • 389
  • 1
  • 3
  • 10
  • 2
    You are looking for the mysql equivalent of pivot or mysql conditional aggregation. – P.Salmon May 11 '18 at 07:40
  • 1
    Possible duplicate of [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) – P.Salmon May 11 '18 at 07:41

1 Answers1

1

You can't really do pivot tables in MySQL. However with a fixed number of columns (such as expected output #1) you can simulate them with CASE statements e.g.

select date_format(date, '%d%m%Y') as Date,
   sum(case when level=1 then count else 0 end) as `1`,
   sum(case when level=2 then count else 0 end) as `2`,
   sum(case when level=3 then count else 0 end) as `3`,
   sum(case when level=4 then count else 0 end) as `4`,
   sum(case when level=5 then count else 0 end) as `5`
from table1
group by Date

Output:

Date        1   2   3   4   5
11052018    0   0   14  0   22
12052018    0   14  14  0   0
13052018    12  11  0   0   0
Nick
  • 138,499
  • 22
  • 57
  • 95
  • Did this help? If not could you provide more information to help answer the question? – Nick May 14 '18 at 02:46