CODEWITHSUNDEEP
mysql
enkiki
enkiki

Reputation: 399

MySql: Count occurrences of values by date

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!

Upvotes: 1

Views: 513

Answers (1)

Nick
Nick

Reputation: 147156

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

Upvotes: 1

Related Questions