0

I have tried IsNull, Case statements and searched online, but for some reason if no records are found it still doesn't show a record or replace it with a '0' I'm trying to get all of the Types from the table and display how many have been done within a date range.

Here is my code so far.

SELECT     ISNULL(COUNT(Type), 0) AS Total, Type
FROM         dbo.Refs
WHERE     (DateSubmitted BETWEEN CONVERT(DATETIME, '2014-02-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-01-01 00:00:00', 102))
GROUP BY Type

The Type column is a varchar to unfortunaltey

Thanks

Adamsk1
  • 67
  • 8

2 Answers2

2

If you have a table that stores your types you can use:

SELECT  COUNT(r.Type) AS Total, t.Type
FROM    TypeTable t
        LEFT JOIN dbo.Refs r
            ON r.Type = t.Type 
            AND r.DateSubmitted >= CONVERT(DATETIME, '2014-02-01 00:00:00', 102) 
            AND r.DateSubmitted <=  CONVERT(DATETIME, '2015-01-01 00:00:00', 102)
GROUP BY t.Type;

Otherwise you will need to use your existing table to get a list of all types, before joining to the table filtered by date:

SELECT  COUNT(r.Type) AS Total, t.Type
FROM    (SELECT DISTINCT Type FROM dbo.Refs) t
        LEFT JOIN dbo.Refs r
            ON r.Type = t.Type 
            AND r.DateSubmitted >= CONVERT(DATETIME, '2014-02-01 00:00:00', 102) 
            AND r.DateSubmitted <=  CONVERT(DATETIME, '2015-01-01 00:00:00', 102)
GROUP BY t.Type;

Alternatively you could use:

SELECT  COUNT(CASE WHEN r.DateSubmitted >= CONVERT(DATETIME, '2014-02-01 00:00:00', 102) 
                        AND r.DateSubmitted <=  CONVERT(DATETIME, '2015-01-01 00:00:00', 102) 
                    THEN 1
                END) AS Total, 
        t.Type
FROM    dbo.Refs r
GROUP BY r.Type;

The best method will depend on your indexes/if you do or don't have a table to store all types.

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • +1 for including the WHERE-part in the join-condition – piet.t Mar 07 '14 at 12:05
  • Thanks for the response. I'm afraid there isn't a look up table, so its down to options 2 and 3 you have given. Although when I try to run the second piece of SQL I am getting the following error? Incorrect syntax near '.'. – Adamsk1 Mar 07 '14 at 12:39
  • I missed out the `FROM` in the subquery!! Shocking! I have corrected it now, so it should run now. – GarethD Mar 07 '14 at 12:41
  • Thank you, made my Friday that little better! Working great. – Adamsk1 Mar 07 '14 at 12:43
0

you cannot use a group by to achieve what you want -See the following post Count Returning blank instead of 0

Community
  • 1
  • 1
Lawrence Thurman
  • 657
  • 8
  • 15