SELECT query with multiple sub-queries for counts

Question

I just wrote this query for a report. But I originally wrote it without the date range filter on every sub-query. But that didn't work. So I added it to each sub-query. And that worked, but I don't really like having to repeat it every time, is there syntax to do the same thing simpler?

 SELECT Count(r.id)                       AS cnt_total,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'notnow') AS cnt_notnow,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'insert') AS cnt_insert,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'update') AS cnt_update,
   (SELECT Count(r1.entity_id)
    FROM   auto_reminders_members r1
    WHERE  r1.reminder_id = r.reminder_id
           AND r1.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013',
                                             101)
                                             AND
               CONVERT(DATETIME,
               '03/28/2013' + ' 23:59:59.997 ', 101)
           AND r1.action = 'verify') AS cnt_verify
FROM   auto_reminders_members r
WHERE  r.reminder_id = 1
       AND r.date_last_reminder BETWEEN CONVERT(DATETIME, '03/28/2013', 101) AND
                                            CONVERT(DATETIME,
                                            '03/28/2013' + ' 23:59:59.997 ', 101
                                            )
GROUP  BY r.reminder_id  

Solution 1

Here is a way to do that without all the sub-queries

SELECT  Count(r.id) AS cnt_total,
        sum(case when r.action = 'notnow' then 1 else 0 end) as 'cnt_notnow',
        sum(case when r.action = 'insert' then 1 else 0 end) as 'cnt_insert',
        sum(case when r.action = 'update' then 1 else 0 end) as 'cnt_update',
        sum(case when r.action = 'verify' then 1 else 0 end) as 'cnt_verify'        
FROM    auto_reminders_members r

WHERE  r.reminder_id = 1
       AND CONVERT(DATE, r.date_last_reminder) = '20130328'

I also cleaned up the query a bit, removing the group by as this will always be 1, and changing the date comparison to avoid using the messy between logic (Thanks Aaron for the comment)

Solution 2

Here is a way using GROUPING SETS and PIVOT, but this will hinge on the version you are using, which you forgot to tell us:

DECLARE @reminder_id INT, @date DATE;

SELECT @reminder_id = 1, @date = '20130328';

;WITH x AS 
(
  SELECT [action] = COALESCE([action],'Total'), c2 = COUNT(*)
    FROM dbo.auto_reminders_members
    WHERE reminder_id = @reminder_id
    AND CONVERT(DATE, date_last_reminder) = @date
    GROUP BY GROUPING SETS(([action]), ())
)
SELECT reminder_id = @reminder_id, * FROM x 
PIVOT 
(
  MAX([c2]) FOR [action] IN ([Total],[notnow],[insert],[update],[verify])
) AS p;

If 2005, you can replace the GROUPING SETS line with the old-style syntax:

    GROUP BY [action] WITH ROLLUP

I really wish people would stop condoning BETWEEN and this magic "end of day" stuff. If you want a full day your WHERE clause should either be:

WHERE CONVERT(DATE, r.date_last_reminder) = '20130328'

Or, if you are on 2005:

WHERE r.date_last_reminder >= '20130328'
  AND r.date_last_reminder <  '20130329'

Some important points here, mostly that 23:59:59.997 may round up or miss data depending on the underlying data type, that there is no reason you should ever be converting a datetime value to a string to perform a query, and that regional formats like m/d/y are bad for a variety of reasons (for example if your query was for 03/08/2013 I wouldn't know if you were after March 8th or August 3rd).

Please read these articles:

• What do BETWEEN and the devil have in common?

• Bad habits to kick : mis-handling date / range queries

You should also be careful about using reserved words / keywords for column names, like entity_id and action.

Comments

[developerCoder]

is sub query part always will be same ?

[Adir D]

What version of SQL Server please?

[Adir D]

FYI the BETWEEN remains terrible, also there is no reason to return the reminder_id column, never mind GROUP BY it, if it's a constant (1).

[Nate]

Right, I didn't pay too much attention to cleaning up that part of the query yet

[pixelwiz]

This is very interesting, I went with the first approach by doing sums, and that worked for this project, but I have a much more complex query I have to do a bunch of different counts on for a different project, where this might be more appropriate. Never seen anything like it. BTW, we are on SQL SErver 2012

[Nate]

Yes, this is what many have posted as the correct solution. You should probably select one of them as the answer.