CODEWITHSUNDEEP
mysqlsqlselect
Riot
Riot

Reputation: 25

How to get maximum number of records per day? max(count) MySQL DB

Good day!

I have a table:

create table table_1 (field_1 varchar (10),  timestamp datetime(3),  field_2  varchar (10));

Date format yyyy-mm-ddthh:mm:ss.000z.

And i need to count the number of records per hour and get the maximum this number of these records per day.

Request:

select date_format(date,'%Y.%m.%d') as date, max(summ)  from (select date_format(timestamp,'%Y.%m.%d %H' ) as date, count(field_2) as summ from table_1 a where field_1 in (1) group by date) b group by date;

Result:

date        summ
2019.12.25  2
2019.12.25  3
2019.12.25  12

But i need sumthing like:

date        summ
2019.12.25  12
2019.12.26  15
2019.12.27  14

Upvotes: 0

Views: 806

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You would use window functions with aggregation:

select t.*
from (select date_format(timestamp, '%Y.%m.%d %H') as date, count(field_2) as summ,
             row_number() over (partition by date(timestamp) order by count(field_2) desc) as seqnum
      from table_1 a
      where field_1 in (1)
      group by date, date(timestamp)
     ) t
where seqnum = 1;

Here is a demonstration that the code runs correctly.

Upvotes: 1

Related Questions