MySQL COUNT(*) GROUP BY HAVING COUNT=?

Question

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

vyegorov · Accepted Answer · 2012-07-01 04:17:01Z

3

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.

answered Jul 1, 2012 at 4:17

vyegorov

23k7 gold badges61 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xdazz · Accepted Answer · 2012-07-01 04:22:40Z

3

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

answered Jul 1, 2012 at 4:22

xdazz

161k38 gold badges255 silver badges278 bronze badges

Collectives™ on Stack Overflow

MySQL COUNT(*) GROUP BY HAVING COUNT=?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related