SQL Query a range of data and return NULL if the data is NOT exist

Question

I'm trying to populate a list of data into a line charts. My X-axis will be my StartTime and my Y-axis will be Total

Wanted to ask is it possible to query a range of data and if the data is not in the database and return it as null instead of no show, take an example below:

|StartTime    |Qty    |
-----------------------
|10           |1      |
|11           |3      |
|12           |2      |
|13           |1      |
|11           |2      |

What's my expected result: WHERE CLAUSE AS StartTime within 9 TO 12

|StartTime    |TOTAL  |
-----------------------
|9            |NULL   |
|10           |1      |
|11           |5      |
|12           |2      |

Can anyone show me and example of what the query will be? Because I have no idea at all.

Do you have a table containing every available StartTime, or is it just assumed/implied, that each hour exists? The answer to this question would determine the query structure and how simple or complex the resulting query will be, — gmiley
– gmiley, Commented Jun 8, 2016 at 3:03
@gmiley Nope, The table does not contain every single StartTime. — J4X
– J4X, Commented Jun 8, 2016 at 3:07

Kasim Husaini · Accepted Answer · 2016-06-08 03:07:30Z

1

You can have another table with Starttime you want to show on graph. Left join with your first table and do a group by starttime from new table. Use the count for your purpose.

TableTimes:

   | StartTime |
   --------------
   |   9        |
   |   10       |
   |   11       |
   |   12       |
   |   13       |
   |   14       |

Select Sum(Qty) From TableTimes TT
Left Join FirstTable FT on TT.StartTime=FT.StartTime
Where TT.StartTime Between 9 and 12
Group by TT.StartTime

edited Jun 8, 2016 at 3:07

answered Jun 8, 2016 at 3:01

Kasim Husaini

4985 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

J4X Over a year ago

so you recommend me to create a TableTimes that list out all available StartTime column ONLY?

Kasim Husaini Over a year ago

Yes that's correct. A new table that lists all times you would like to show in your chart.

gmiley · Accepted Answer · 2016-06-08 03:31:15Z

You can get around creating a brand new table by using a sub-query or a CTE:

select h.Hour, Sum(i.Qty) as Qty
from ItemsPerHour i
right outer join (
  select 1 as Hour union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all 
  select 7 union all
  select 8 union all
  select 9 union all
  select 10 union all
  select 11 union all
  select 12 union all
  select 13 union all
  select 14 union all
  select 15 union all
  select 16 union all
  select 17 union all
  select 18 union all
  select 19 union all
  select 20 union all
  select 21 union all
  select 24
) h
on h.Hour = i.StartTime
order by h.Hour;

Using WITH for an hours CTE:

with hours as
(
  select 1 as Hour union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all 
  select 7 union all
  select 8 union all
  select 9 union all
  select 10 union all
  select 11 union all
  select 12 union all
  select 13 union all
  select 14 union all
  select 15 union all
  select 16 union all
  select 17 union all
  select 18 union all
  select 19 union all
  select 20 union all
  select 21 union all
  select 24
)
select h.Hour, Sum(i.Qty) as Qty
from ItemsPerHour i
right outer join hours h
on h.Hour = i.StartTime
order by h.Hour;

Collectives™ on Stack Overflow

SQL Query a range of data and return NULL if the data is NOT exist

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related