1

I am trying to set up a SQL Server query that lists the count of people signed up for tours during a conference event and then displays the total sum. This is my SQL code that is coming close, but it is ignoring the WHERE clause for the grand total (but not the individual totals):

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(SiteTour) AS Expr1
FROM       tblConference AS tblConference_1

Here is what it is producing:

         0
Tour1   22
Tour2   30
Tour3   30
----------
TOTAL   83

(Tour1 count of 22 is correct, because there is one RegistrationType cancellation. However, the TOTAL of 83 should be 82.)

Also, I am getting an initial row, I guess based on counting NULLs, that shows a 0. Is there some way prevent this?

Please help me correct this. Thank you.

1
  • instead of doing LIKE, you should do = since you are not searching for wildcard %cancellation%. – Kin Shah Jun 28 '18 at 15:32
5

You need to add the same where clause in your union select.

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour
UNION ALL
SELECT     'TOTAL' AS SiteTour, COUNT(tblConference_1.SiteTour) AS Expr1
FROM       tblConference AS tblConference_1
WHERE      (tblConference_1.RegistrationType NOT LIKE 'cancellation')
4
  • 1
    Face palm. Thanks, that is it! Is there some way to get rid of the initial 0 row? – J Duke Jun 28 '18 at 15:34
  • 1
    @JDuke - After the GROUP BY, try HAVING COUNT(SiteTour) > 0 – RDFozz Jun 28 '18 at 15:42
  • 2
    How can you get a row with a zero count? That can only happen with an outer join, not a query of a single table. – Barmar Jun 28 '18 at 17:21
  • @Barmar - COUNT(myColumn) will only count non-null values. COUNT(*) will count the presence of a row. – CElliott Jun 28 '18 at 21:12
5

You should avoid doing the COUNT twice.

;WITH Counts AS
(
    SELECT     SiteTour, COUNT(SiteTour) AS count
    FROM       tblConference
    WHERE      RegistrationType <> 'cancellation' AND SiteTour IS NOT NULL
    GROUP BY   SiteTour
),
Totals AS
(
    SELECT
        C.SiteTour,
        C.count
    FROM
        Counts AS C
    UNION ALL
    SELECT     
        'TOTAL' AS SiteTour, 
        SUM(C.count) AS count
    FROM
        Counts AS C
)
SELECT
    T.SiteTour,
    T.Count
FROM
    Totals AS T
ORDER BY
    CASE 
        WHEN T.SiteTour <> 'TOTAL' THEN 1
        ELSE 2 END,
    T.SiteTour

This way all your filters are written only once too.

3
  • While I agree that this is cleaner code, be aware that SQL won't reuse the result from Counts, it will get the data from the table twice. – AlwaysLoadingData Jun 28 '18 at 19:10
  • @AlwaysLoadingData Actually, that depends on what the optimizer decides based on it's statistical knowledge of the tables. AFAIK, it is possible that it could decide to spool the intermediate results and then reuse them. – RBarryYoung Jun 28 '18 at 21:21
  • (well, maybe, not sure about this query) – RBarryYoung Jun 28 '18 at 21:29
1

The first part of the query has a Where condition

SELECT     SiteTour, COUNT(SiteTour) AS count
FROM       tblConference
WHERE      (RegistrationType NOT LIKE 'cancellation')
GROUP BY   SiteTour

the second part has none

SELECT     'TOTAL' AS SiteTour, COUNT(SiteTour) AS Expr1
FROM       tblConference AS tblConference_1

So it's not ignoring the where. Simply, there is no where.

Not the answer you're looking for? Browse other questions tagged or ask your own question.