CODEWITHSUNDEEP
mysqlcountgroup-byhaving-clause
user1478893
user1478893

Reputation: 41

MySQL COUNT(*) GROUP BY HAVING COUNT=?

This my Query

SELECT COUNT(*) as total, toys, date FROM T1
 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
UNION
SELECT COUNT(*) as total, toys, date FROM T2
 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
UNION
SELECT COUNT(*) as total, toys, date FROM T3
 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
GROUP BY RoomType
HAVING COUNT( total ) = 4

Output result

count   Toys            date
3   Bibi        2012-06-26
4   Baba            2012-06-26

How can i get MYSQL to show results only for count=4

Upvotes: 2

Views: 37515

Answers (2)

xdazz
xdazz

Reputation: 160863

You need group by first then union the result.

SELECT COUNT(*) as total, toys, date FROM T1
 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
 GROUP BY RoomType HAVING COUNT( *) = 4
UNION
SELECT COUNT(*) as total, toys, date FROM T2
 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
 GROUP BY RoomType HAVING COUNT( * ) = 4
UNION
SELECT COUNT(*) as total, toys, date FROM T3
 WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
 GROUP BY RoomType HAVING COUNT( * ) = 4

Upvotes: 3

vyegorov
vyegorov

Reputation: 22895

SELECT * FROM (
    SELECT COUNT(*) as total, toys, date FROM T1
     WHERE (date >= '2012-06-26' AND date < '2012-06-30') AND (Avail > '0')
     GROUP BY RoomType
    UNION
    SELECT COUNT(*) as total, toys, date FROM T2
     WHERE (date >= '2012-06-26' AND date < '2012-06-30') AND (Avail > '0')
     GROUP BY RoomType
    UNION
    SELECT COUNT(*) as total, toys, date FROM T3
     WHERE (date >= '2012-06-26'AND date < '2012-06-30') AND (Avail > '0')
    GROUP BY RoomType) AS src
WHERE total = 4;

Please, note, that for proper data groupping you must have all columns either in the GROUP BY clause or as arguments to the aggregate functions. It is MySQL feature to allow you to avoid this restriction, but it might lead you to the unexpected results.

Upvotes: 3

Related Questions