CODEWITHSUNDEEP
phpmysqlsql
Chip
Chip

Reputation: 47

Sql COUNT with HAVING clause producing wrong result

Record in my mysql database

id  camper_id   reg_date
1   3           2017-04-17 00:00:00
2   3           2017-04-18 00:00:00
3   4           2017-04-15 00:00:00
4   4           2017-04-13 00:00:00

SELECT COUNT(*),camper_id,reg_date FROM tbl_registration GROUP BY camper_id HAVING reg_date >= CURDATE()

lets say today or CURDATE is 2017-04-15 00:00:00

I am getting the wrong result This is what I get when I run the query

COUNT(*)    campr_id    reg_date
2           3           2017-04-15 00:00:00
2           4           2017-04-18 00:00:00

I should be getting..

COUNT(*)    camper_id   reg_date
2           3           2017-04-15 00:00:00
1           4           2017-04-18 00:00:00

what is wrong with my query ?

Upvotes: 0

Views: 552

Answers (2)

toonice
toonice

Reputation: 2236

Please try the following...

SELECT COUNT(*),
       camper_id,
       reg_date
FROM tbl_registration
WHERE DATEDIFF( reg_date,
                CURDATE() ) >= 0
GROUP BY camper_id;

Please read dasblinkenlight's answer at SQL - having VS where for an excellent discussion of the difference between WITH and HAVING.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269963

This is your query:

SELECT COUNT(*), camper_id, reg_date
FROM tbl_registration
GROUP BY camper_id
HAVING reg_date >= CURDATE();

The HAVING is processed after the GROUP BY. But you have not specified reg_date in the GROUP BY. Hence, you should have an aggregation function around it.

I think you really want a WHERE clause:

SELECT COUNT(*), camper_id, MIN(reg_date) as reg_date
FROM tbl_registration
WHERE reg_date >= CURDATE()
GROUP BY camper_id;

MySQL allows you to have such "bare" columns in an aggregation. However, it is a really bad practice. I think the most recent version of MySQL has changed the default behavior to not allow this construct -- conforming better to ANSI SQL and to how other databases work.

Upvotes: 1

Related Questions