How to show 0 value using COUNT and SELECTon a SQL query

Question

I have ONLY 1 table called Meeting that stores all meeting requests. This table can be EMPTY.

It has several columns including requestType (which can only be "MT") meetingStatus (can only be either pending, approved, denied or canceled) and meetingCreatedTime

I want to count how many requests of each status's type (in other words how many requests are pending, how many are approved, denied and canceled) for the last 30 days

Problem is that if there is no request then nothing display but I want to display 0, how do I do it? Here is my query now:

SELECT [requestType], 
  ( SELECT COUNT ([requestType]) FROM [Meeting] WHERE CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) AND [meetingStatus] = 'Approved') As 'Approved',
  ( SELECT COUNT ([requestType]) FROM [Meeting] WHERE CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) AND [meetingStatus] = 'Pending') As 'Pending',
  ( SELECT COUNT ([requestType]) FROM [Meeting] WHERE CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) AND [meetingStatus] = 'Canceled') As 'Canceled',
  ( SELECT COUNT ([requestType]) FROM [Meeting] WHERE CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) AND [meetingStatus] = 'Denied') As 'Denied'
  FROM [Meeting] 
  WHERE CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) GROUP BY [requestType]

Result:

What I want is:

What I want is that: if there is no records that meet the criteria then display `0` — Ronaldinho Learn Coding, Feb 02 '16 at 19:40
While you're at it, [use pivot instead](http://stackoverflow.com/q/15931607/521799) — Lukas Eder, Feb 02 '16 at 20:40

Tom H · Accepted Answer · 2016-02-02T20:05:30.330

3

SELECT
    RT.requestType,
    SUM(CASE WHEN M.meetingStatus = 'Approved' THEN 1 ELSE 0 END) AS Approved,
    SUM(CASE WHEN M.meetingStatus = 'Pending' THEN 1 ELSE 0 END) AS Pending,
    SUM(CASE WHEN M.meetingStatus = 'Canceled' THEN 1 ELSE 0 END) AS Canceled,
    SUM(CASE WHEN M.meetingStatus = 'Denied' THEN 1 ELSE 0 END) AS Denied,
FROM
    (SELECT DISTINCT requestType FROM Meeting) RT
LEFT OUTER JOIN Meeting M ON
    M.requestType = RT.requestType AND
    M.meetingCreatedTime >= DATEADD(DAY, -30, GETDATE())
GROUP BY
    RT.requestType

The SUMs are a much clearer (IMO) and much more efficient way of getting the counts that you need. Using the requestType table (assuming that you have one) lets you get results for every request type even if there are no meetings of that type in the date range. The LEFT OUTER JOIN to the meeting table allows the request type to still show up even if there are no meetings for that time period.

All of your CASTs between date values seem unnecessary.

edited Feb 02 '16 at 20:05

answered Feb 02 '16 at 19:40

Tom H

46,766
14
87
128

I only have 1 table `Meeting` – Ronaldinho Learn Coding Feb 02 '16 at 19:44
Looks like the database is poorly designed then. I'll adjust the code to handle the single table, but if you can add tables to hold the meeting types and request types then you should consider it. – Tom H Feb 02 '16 at 19:51
@TomH: If 'MT' is the only value for requestType (as indicated in the question), then what's the real purpose of a lookup table? – Ellesedil Feb 02 '16 at 19:54
If MT is the only value for requestType then what's the real purpose of the column? – Tom H Feb 02 '16 at 19:55
That's definitely a good question, which we can't answer because we don't have the requirements that resulted in the design of the table. In either case, this answer will still result in empty results because the question's scenario is that the Meeting table is empty. But, a lookup table would have solved this if it existed. – Ellesedil Feb 02 '16 at 19:57
@TomH Did you edit your answer to make it work for 1 table (since I dont have any look up table, where is `Request_Type` from? – Ronaldinho Learn Coding Feb 02 '16 at 20:00
There is a subquery which returns all of the possible values of requestType from your Meeting table (just realized that I put an underscore in the name by mistake, I'll correct that). If you're only going to ever have 'MT' then that can simply be `(SELECT 'MT' AS requestType)`, but my answer solves the more general case where that column actually holds meaningful data. – Tom H Feb 02 '16 at 20:05
@TomH About the `CAST`, so you mean that `meetingCreatedTime >= DATEADD(DAY, -30, GETDATE())` is as same as `CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) ` ? – Ronaldinho Learn Coding Feb 02 '16 at 20:27
Casting them as dates removes the time portion, so if that's relevant in your data then they are not quite the same in some edge cases. This also assumes that meetingCreatedTime is actually a `DATETIME` column and not defined as a `VARCHAR` that's actually holding `DATETIME`s – Tom H Feb 02 '16 at 20:32
@TomH, I like the `SUM()` approach, upvote from my side... Still I'd prefer the `UNION` approach as it does not need any second table with existing data. – Shnugo Feb 03 '16 at 13:13

nathan_jr · Answer 2 · 2016-02-02T19:46:27.763

0

Move those subqueries into simple sum/case statements:

 select rt.request_type,
            sum(case when [meetingStatus] = 'Approved' then 1 else 0 end),
            sum(case when [meetingStatus] = 'Pending' then 1 else 0 end),
            sum(case when [meetingStatus] = 'Canceled' then 1 else 0 end),
            sum(case when [meetingStatus] = 'Denied' then 1 else 0 end)
    from    (   select 'MT' ) rt (request_type) --hopefully you have lookup table for this
    left
    join    [Meeting] m on
            rt.request_type = m.request_type and
            CAST([meetingCreatedTime] AS DATE) >= CAST(DateAdd(DAY,-30,Getdate()) AS DATE) 
    group
    by      rt.request_type;

edited Feb 02 '16 at 19:46

answered Feb 02 '16 at 19:40

nathan_jr

9,092
3
40
55

The issue is that Meeting table is empty. But, requestType can only have one value. I suggest defaulting it somehow. – Ellesedil Feb 02 '16 at 19:45
@Ellesedil I see, I have added example to default to MT – nathan_jr Feb 02 '16 at 19:51
Hey I only have 1 table, I dont have any `request_type` table, can you update your answer? – Ronaldinho Learn Coding Feb 02 '16 at 20:03
@RonaldinhoLearnCoding the request_type table in my answer is derived-- should work ok for you – nathan_jr Feb 02 '16 at 20:04

score 0 · Answer 3 · answered Feb 02 '16 at 20:54

This is one possible approach to force one line to be visible in any case. Adapt this to your needs...

Copy it into an empty query window and execute... play around with the WHERE part...

DECLARE @Test TABLE (ID INT IDENTITY, GroupingKey VARCHAR(100));
INSERT INTO @Test VALUES ('a'),('a'),('b');


SELECT TOP 1 tbl.CountOfA
            ,tbl.CountOfB
            ,tbl.CountOfC
FROM
( 
    SELECT 1 AS Marker
         ,(SELECT COUNT(*) FROM @Test WHERE GroupingKey='a') AS CountOfA
         ,(SELECT COUNT(*) FROM @Test WHERE GroupingKey='b') AS CountOfB
         ,(SELECT COUNT(*) FROM @Test WHERE GroupingKey='c') AS CountOfC
    WHERE (1=1) --play here with (1=0) and (1=1)
    UNION ALL 
    SELECT 2,0,0,0
) AS tbl
ORDER BY Marker

How to show 0 value using COUNT and SELECTon a SQL query

3 Answers3