Prajith A S Prajith A S - 5 months ago 10
MySQL Question

How to get the count of records with the highest date as a given date in mysql query

I have a table with 2 fields called

clc_cs_id,clc_pt_nxt_rv_dt
in my table. I am trying to get the count of
clc_cs_id
's having the highest date is
2016-06-25
from my below example. I have tried to give the
max(clc_pt_nxt_rv_dt)='2016-06-25'
in condition. But throwing
invalid use of group by function
error.
Sample data given below.

clc_cs_id clc_pt_nxt_rv_dt
--------- -------------------
2 25-06-2016 00:00:00
2 27-06-2016 00:00:00
3 17-06-2016 00:00:00
3 25-06-2016 00:00:00
3 29-06-2016 00:00:00
3 29-06-2016 00:00:00
4 18-06-2016 00:00:00
4 25-06-2016 00:00:00
5 25-06-2016 00:00:00


From the above i am trying to exclude
clc_cs_id 3
, because the highest date for that is 29.06.2016. The query which i tried is given below.

select count(clc_cs_id) from clc_case_dtls
where max(clc_pt_nxt_rv_dt) = date('2016-06-25')
group by clc_cs_id


If anybody can help, it will be very helpful for me.
Thanks in advance.

The expected output is

COUNT(clc_cs_id)
---------------
3

Answer

Your original query was not far off. I think you want to use HAVING to restrict each group in the query:

SELECT clc_cs_id, COUNT(clc_cs_id)
FROM clc_case_dtls
GROUP BY clc_cs_id
HAVING MAX(clc_pt_nxt_rv_dt) = STR_TO_DATE('2016-06-25', '%Y-%m-%d')

I also replaced your date() call with STR_TO_DATE().

If you want to get the total number of clc_cs_id values whose max date is 2016-06-25, then you can query the above query:

SELECT COUNT(*)
FROM
(
    SELECT clc_cs_id, COUNT(clc_cs_id)
    FROM clc_case_dtls
    GROUP BY clc_cs_id
    HAVING MAX(clc_pt_nxt_rv_dt) = STR_TO_DATE('2016-06-25', '%Y-%m-%d')
) t