CODEWITHSUNDEEP
mysql
lennert_h
lennert_h

Reputation: 213

GROUP results from table on date with data from different rows (with the same date)

I have this table where I store the number of visitors per day for each location:

id      datum           dag     campus      bezoekers
"1"     "2018-04-19"    "4"     "knt"       "755"
"2"     "2018-04-19"    "4"     "mar"       "238"
"3"     "2018-04-19"    "4"     "kat"       "335"
"6"     "2018-04-20"    "5"     "knt"       "736"
"7"     "2018-04-20"    "5"     "mar"       "66"
"8"     "2018-04-20"    "5"     "kat"       "327"
"9"     "2018-04-21"    "6"     "knt"       "0"
"10"    "2018-04-21"    "6"     "mar"       "0"
"11"    "2018-04-21"    "6"     "kat"       "0"

fig 1.

I would like to query this table, so that I get this as a result:

DATUM       KAT     KNT     MAR
19/04/2018  335     755     238
20/04/2018  327     736     66
21/04/2018  0       0       0

fig 2

(So for each date one row, with the number of visitors for each location in a column , with the location name as the column name)

So far I tried this:

SELECT
datum
,
    CASE
        WHEN campus = "knt" THEN bezoekers      
    END  AS "KNT"
,   
    CASE
        WHEN campus = "kat" THEN bezoekers  
    END  AS "KAT"
,
    CASE
        WHEN campus = "mar" THEN bezoekers
    END         AS "MAR"
FROM telling_dag

fig 3

But this (logically) results in : 

datum           KNT     KAT     MAR
"2018-04-19"    "755"   \N      \N
"2018-04-19"    \N      \N      "238"
"2018-04-19"    \N      "335"   \N
"2018-04-20"    "736"   \N      \N
"2018-04-20"    \N      \N      "66"
"2018-04-20"    \N      "327"   \N
"2018-04-21"    "0"     \N      \N
"2018-04-21"    \N      \N      "0"
"2018-04-21"    \N      "0"     \N

fig 4

So my question is: how can I group the above result (fig 4) in 1 row per date, with the 3 values per location in the colums (as in fig 2)

Upvotes: 0

Views: 32

Answers (1)

A. Colonna
A. Colonna

Reputation: 872

You have to group on the datum column and to get the result you want if there is only one non NULL element you can use MAX:

SELECT
datum
,
   MAX(CASE
        WHEN campus = "knt" THEN bezoekers      
    END)  AS "KNT"
,   
    MAX(CASE
        WHEN campus = "kat" THEN bezoekers  
    END)  AS "KAT"
,
    MAX(CASE
        WHEN campus = "mar" THEN bezoekers
    END)         AS "MAR"
FROM telling_dag
GROUP BY datum

Upvotes: 1

Related Questions