CODEWITHSUNDEEP
sqlsql-serverjoincountaggregate-functions
Ronaldinho Learn Coding
Ronaldinho Learn Coding

Reputation: 13824

How to include 0 in the result of count query

I have 4 tables that stores 4 kind of request: Time-off (TO), Meeting (MT), PO and Travel (TR).

I want to count total requests of each type in each table in the last 30 days:

  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [Timeoff] 
  WHERE [requestType] = 'TO' AND CAST([timeoffCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) GROUP BY [requestType]
  UNION ALL
  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [Meeting] 
  WHERE [requestType] = 'MT' AND CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) GROUP BY [requestType]
  UNION ALL
  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [PO] 
  WHERE [requestType] = 'PO' AND CAST([poCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) GROUP BY [requestType]
  UNION ALL
  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [Travel] 
  WHERE [requestType] = 'TR' AND CAST([travelCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) GROUP BY [requestType]

Problem is that, if the count is 0 then it won't show up at all in the result, for example if Meeting requests's count = 0 then I only have this as result:

enter image description here

How do I include the 0 value in the result, so I will have something like that (if Meeting MT count = 0):

requestType --- Count

TO ------------- 30

MT ------------- 0

PO -------------19

TR -------------9

Upvotes: 0

Views: 153

Answers (2)

Atheer Mostafa
Atheer Mostafa

Reputation: 735

You have to remove the GROUP BY from your queries, it is not needed! your query should simply be :

SELECT 'TO' AS [requestType], COUNT ([requestType]) As 'Count' 
  FROM [Timeoff] 
  WHERE [requestType] = 'TO' AND CAST([timeoffCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE)
  UNION ALL
  SELECT 'Meeting' AS [requestType], COUNT ([requestType]) As 'Count' 
  FROM [Meeting] 
  WHERE [requestType] = 'Meeting' AND CAST([meetingCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE)
  UNION ALL
  SELECT 'PO' AS [requestType], COUNT ([requestType]) As 'Count' 
  FROM [PO] 
  WHERE [requestType] = 'PO' AND CAST([poCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE)
  UNION ALL
  SELECT 'TR' AS [requestType], COUNT ([requestType]) As 'Count' 
  FROM [Travel] 
  WHERE [requestType] = 'TR' AND CAST([travelCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE)

Upvotes: 1

Jakub Kania
Jakub Kania

Reputation: 16477

You can either do an outer join on each query to something like (select 1)

SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM (select 1 as foo) bar
  LEFT JOIN [CTEite].[dbo].[Timeoff] ON 1=1
  WHERE [requestType] = 'TO' AND CAST([timeoffCreaime] ATE) >= CASt(DAY,-

or UNION and sum

WITH foo AS(
SELECT 'TO' as [requestType], 0 as [Count]
UNION ALL
SELECT 'MT', 0
UNION ALL
....
)
SELECT [requestType], sum([Count])
FROM
(SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [CTEite].[dbo].[Timeoff] 
  WHERE [requestType] = 'TO' AND CAST([timeoffCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE) GROUP BY [requestType]
  UNION ALL
  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [CTEite].[dbo].[Meeting] 
  WHERE [requestType] = 'Meeting' AND CAST([meetingCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE) GROUP BY [requestType]
  UNION ALL
  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [CTEite].[dbo].[PO] 
  WHERE [requestType] = 'PO' AND CAST([poCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE) GROUP BY [requestType]
  UNION ALL
  SELECT [requestType], COUNT ([requestType]) As 'Count' 
  FROM [CTEite].[dbo].[Travel] 
  WHERE [requestType] = 'TR' AND CAST([travelCreaime] ATE) >= CASt(DAY,-30,Gte()) ATE) GROUP BY [requestType]
 UNION SELECT * FROM foo)
GROUP BY [requestType]

Upvotes: 0

Related Questions