0

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 ?

Chip
  • 37
  • 7
  • What if you pull the `GROUP BY` part as the last one ? And why don't use `WHERE` clause ? – JazZ Apr 16 '17 at 01:51

2 Answers2

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Your query produces the required result when I run it directly in PhpMyadmin but when I use the same query in php code it produces the Undefined index: COUNT(reg_date) error – Chip Apr 16 '17 at 02:04
  • Thank you its working fine after modifying a it at my end. Thank you very much. – Chip Apr 16 '17 at 02:19
0

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.

Community
  • 1
  • 1
toonice
  • 2,211
  • 1
  • 13
  • 20
  • I get unexpected ordering of clauses (near GROUP BY) in PhpMyadmin – Chip Apr 16 '17 at 02:04
  • Your query also producing the required result. Thank you too as wel and I appreciate the link for the discussion . – Chip Apr 16 '17 at 02:21