CODEWITHSUNDEEP
mysql
AllenBooTung
AllenBooTung

Reputation: 340

Count grouped rows in a time range

I'm having trouble with a query. I need to get the count in a grouped row. The final result should look like this

id  title   record_date record_timerange    count
109 game    2018-07-09  12:00-13:00         1
106 game    2018-07-09  11:00-12:00         3
116 book    2018-07-09  08:00-09:00         3
104 game    2018-07-09  08:00-09:00         2
115 game    2018-07-08  12:00-13:00         1
112 game    2018-07-08  11:00-12:00         3
110 game    2018-07-08  08:00-09:00         2

This is my sql and fiddle http://sqlfiddle.com/#!9/9d27d9/1

Select id, title, 
DATE_FORMAT(record_datetime, '%Y-%m-%d')as record_date, 
Concat(DATE_FORMAT(record_datetime, '%H'),':00-', LPAD( DATE_FORMAT(record_datetime, '%H')+01,2,0 )  ,':00')as record_timerange,
(select count(id) from nav_static_data as sub where 1=1
    and sub.title=title 
    and DATE_FORMAT(sub.record_datetime,'%H')=record_date
    and DATE_FORMAT(sub.record_datetime, '%Y-%m-%d')=DATE_FORMAT(record_datetime, '%Y-%m-%d')
)as count

from nav_static_data as m where 1=1  
group by FLOOR(MOD(m.record_datetime, 86400)/8640), m.title
order by record_date desc, record_timerange desc, m.title

How can I fix the count column and a missing row(id=110)?

Upvotes: 0

Views: 32

Answers (2)

Gaj
Gaj

Reputation: 886

Try this

Select id, title, DATE_FORMAT(record_datetime, '%Y-%m-%d')as record_date, 
Concat(DATE_FORMAT(record_datetime, '%H'),':00-',  LPAD( DATE_FORMAT(record_datetime, '%H')+01,2,0 )  ,':00')as record_timerange,
(select count(id) from nav_static_data as sub 
 where sub.title=m.title 
    and DATE_FORMAT(sub.record_datetime, '%H')= DATE_FORMAT(m.record_datetime, '%H')
    and DATE_FORMAT(sub.record_datetime, '%Y-%m-%d')=DATE_FORMAT(m.record_datetime, '%Y-%m-%d')
)as count
from nav_static_data as m
group by FLOOR(mod(m.record_datetime, 86400)/1440), m.title
order by record_date desc, record_timerange desc, m.title

Upvotes: 0

Fahad Anjum
Fahad Anjum

Reputation: 1256

Please try below query. 

select title,
DATE_FORMAT(record_datetime, '%Y-%m-%d')as record_date, 
Concat(DATE_FORMAT(record_datetime, '%H'),':00-',  LPAD( DATE_FORMAT(record_datetime, '%H')+01,2,0 )  ,':00')as record_timerange,
count(title)
from nav_static_data
group by title, record_date,record_timerange
order by id

You can get the result by simply using group by and you do need to use sub query for counting the result.

Upvotes: 2

Related Questions