1

I have a sql query for mysql

select sum(quantity), Hour(posted)
from orders
where posted between '05-10-2014' and '05-10-2014' // my timestamp here
group by Hour(posted)

result may be 

sum, hour
10, 0
12, 1
13, 3 // note 2 missing in hours
13, 5 // note hour 4 is missing

what I need is 

sum,hour
10,0
12,1
0, 2 // 0 for missing hour ( where no record was found in that hour)
13,3 
0, 4 // 0 for missing hour ( where no record was found in that hour)
13,5

how can I do it?? any help is appreciated

Zaffar Saffee
  • 6,167
  • 5
  • 39
  • 77
  • Create a table with numbers from 1 to 24 (or however you want it) and `LEFT JOIN` it to your table. – PM 77-1 Jun 10 '16 at 23:46
  • in a case, where I have a large data set, add one more `LEFT JOIN`seems un-reasonable to me, specially, when it will contain nothing except 1 to 24.. May be I am wrong in thinking that..but that's what I have in mind right now.. – Zaffar Saffee Jun 10 '16 at 23:51
  • 1
    Yes, you are clearly wrong. See http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range or http://stackoverflow.com/q/18812937/2055998 – PM 77-1 Jun 10 '16 at 23:53
  • @PM77-1 and you are extremely right bro...second link is the answer to what I am looking for..can you please post that as an answer? – Zaffar Saffee Jun 10 '16 at 23:58
  • `posted between '' and ''` doesn't make any sense. What are you trying to do with that? Also: which DBMS are you using? –  Jun 11 '16 at 18:20
  • I am using mysql and sorry, made copy paste error, this was time range for me – Zaffar Saffee Jun 15 '16 at 21:04

3 Answers3

0

If you are using SqlServer you can use the following TSQL query to get the desired results:

with [hours] as 
( SELECT DISTINCT hour = number 
FROM master..[spt_values] 
WHERE number BETWEEN 1 AND 24
)

select ISNULL(sum(orders.quantity),0) as quantity, [hours].[hour]
from hours left join orders  on hours.hour =   datepart(hour,posted)
group by [hours].[hour]
Sam
  • 2,935
  • 1
  • 19
  • 26
0

Group by all keeps groups which was filtered in the where clause with value 0:

select sum(quantity), Hour(posted)
from orders
where posted between '' and ''
group by all Hour(posted)
Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
0

No need to create physical table which contains nothing more than 1, 2, 3... Create it with CTE.

with hours as (
select 0 hour
union all
select hour+1 from hours
where hour < 23
)
select hour, isnull(sum(quantity), 0) qty 
from hours h 
left join orders o on h.hour = o.Hour(posted)
where  posted between '05-10-2014' and '05-10-2014' --your range
group by h.hour
Alex Kudryashev
  • 9,120
  • 3
  • 27
  • 36