CODEWITHSUNDEEP
mysqlmax
Prajith A S
Prajith A S

Reputation: 477

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_dtin 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

Upvotes: 0

Views: 643

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

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

Upvotes: 1

Related Questions