Search code examples
mysqlsqlselect-query

MySQL select group by having column != x

I'm trying to select rows where a certain column does not have a certain value, such as 0, but I'm unable to get this to work.

SELECT *
FROM rentals
GROUP BY date, rooms, price 
HAVING show_independently < 1

If show_independently is 1, then I don't want to group them. However, this statement shows no rows even though most rows have show_independently as 0.

Solution

  • If you only want to group some rows and leave others ungrouped, you can use a UNION:

    SELECT *
FROM rentals
WHERE show_independently <> 1
GROUP BY date, rooms, price 
UNION ALL
SELECT *
FROM rentals
WHERE show_independently = 1

    This groups only those where show_independently is not 1, and includes the rest without grouping them.