Pixeldoris Pixeldoris - 6 months ago 8
SQL Question

SQL CASE, when column category = x group by category else don't group

I have this table:

date client category
-------------------------------------
2016-05-20 harrys bar
2016-05-20 pitchers bar
2016-05-20 eagle sport
2016-05-21 claire other
2016-05-21 maximus other
2016-05-24 eagle sport
2016-05-24 dolphin sport


now the outcome is:

date client category
-------------------------------------
2016-05-20 harrys bar (two rows)
2016-05-20 eagle sport
2016-05-21 claire other (two rows)
2016-05-24 eagle sport (two rows)


since I group bu the column category but I want the outcome to be:

date client category
-------------------------------------
2016-05-20 harrys bar (two rows)
2016-05-20 eagle sport
2016-05-21 claire other
2016-05-21 maximus other
2016-05-24 eagle sport
2016-05-24 dolphin sport


So when the date and the category 'bar' match I want them to group. But I don’t want the category 'sport' nor 'other' to group when the date match.

I’ve tried locked in to
CASE-WHEN-THEN-ELSE
..etc. but I just don’t know how to write the sql statment.

I tried something like

SELECT *
FROM events
GROUP BY
CASE
WHEN category = "bar" THEN category ELSE ""
END


which give me:

date client category
-------------------------------------
2016-05-20 harrys bar
2016-05-20 eagle sport


I also tried

WHEN category = "bar" THEN date, category ELSE ""


but it didn't work at all..

as you can see I have no idea how to use
CASE
… so any suggestions?

I've tried to read so many questions here at stackoverflow and also the manual for SQL statments etc. etc. I just don't understand how to use it.

Answer

You could use UNION ALL:

SELECT category, date, client
FROM events
WHERE category <> 'bar'
UNION ALL
SELECT category, date, MIN(client) AS client
FROM events
WHERE category = 'bar'
GROUP BY category, date;

LiveDemo

Comments