CODEWITHSUNDEEP
sql-server
J4X
J4X

Reputation: 119

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

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.

Upvotes: 1

Views: 286

Answers (2)

gmiley
gmiley

Reputation: 6604

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;

Upvotes: 1

Kasim Husaini
Kasim Husaini

Reputation: 422

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

Upvotes: 1

Related Questions