Get SUM of query result WITHIN same query

Question

I have a query:

SELECT case

when Submission__bTracking = 'Phone' then 'Phone'
when Submission__bTracking = 'Web' then 'Web'
when Submission__bTracking = 'Email' then 'Email
when Submission__bTracking = 'Live__bTech__bSupport' then '@ Live Tech Support
when Submission__bTracking = 'Verbal' then 'Verbal Request'
when Submission__bTracking = 'Fax__b__f__bform' then 'Fax / Form'

End as Sub_Tracking,

COUNT(Submission__bTracking) as tickets FROM dbo.MASTER30

WHERE mrSUBMITDATE >= (CONVERT (date, CURRENT_TIMESTAMP -1))
AND mrSUBMITDATE < (CONVERT (date, CURRENT_TIMESTAMP))

GROUP BY Submission__bTracking

WHICH PRODUCES THE FOLLOWING RESULT:

Sub_Tracking               tickets
Email                        36
Fax / Form                    1
@ Live Tech Support          18
Phone                       441
Web                          41

How do I also produce the total sum of all the tickets within the same query?

LIKE BELOW:

Sub_Tracking               tickets
Email                        36
Fax / Form                    1
@ Live Tech Support          18
Phone                       441
Web                          41
ALL                         537

Thanks for any help!!

Maybe just do union and select one more row which sums it all? — buhtla, Dec 24 '14 at 23:15

buhtla · Accepted Answer · 2014-12-24T23:44:12.557

2

You can use UNION and just add one more row in your result set:

YOUR QUERY

UNION ALL
select 'ALL' as Sub_Tracking,
COUNT(Submission__bTracking) as tickets FROM dbo.MASTER30

WHERE mrSUBMITDATE >= (CONVERT (date, CURRENT_TIMESTAMP -1))
AND mrSUBMITDATE < (CONVERT (date, CURRENT_TIMESTAMP))

edited Dec 24 '14 at 23:44

answered Dec 24 '14 at 23:17

buhtla

2,819
4
25
38

The only thing I might change here (and this is mega nitpicky) is the use of UNION ALL vs UNION (eg http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all). But otherwise this will work and I'd probably prefer it to WITH ROLLUP (although the later is a valid option as well) – Evan Volgas Dec 24 '14 at 23:25
You're right, UNION ALL is more efficient, but in this case not relevant since amount of data is very small (7 rows in total). But thanks for the hint anyway. – buhtla Dec 24 '14 at 23:30
It is a super nitpicky detail. No argument about that. And I upvoted your response despite that nitpicky detail. – Evan Volgas Dec 24 '14 at 23:32
1

Yeah, no problem, and thanks for pointing that out, it's a good detail no doubt and worth having in mind always. – buhtla Dec 24 '14 at 23:33
Awesome!! It worked! Thanks so much buhtla and evanv!! – Sung Dec 25 '14 at 04:34

score 1 · Answer 2 · answered Dec 24 '14 at 23:16

1

You could do

...previous lines omitted
GROUP BY Submission__bTracking
UNION
SELECT 'ALL' as Sub_Tracking, count(Submission__bTracking) as tickets
FROM dbo.MASTER30
WHERE mrSUBMITDATE >= (CONVERT (date, CURRENT_TIMESTAMP -1))
AND mrSUBMITDATE < (CONVERT (date, CURRENT_TIMESTAMP))

answered Dec 24 '14 at 23:16

Paul Abbott

7,065
3
27
45

The only thing I might change here (and this is mega nitpicky) is the use of UNION ALL vs UNION (eg http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all). But otherwise this will work and I'd probably prefer it to WITH ROLLUP (although the later is a valid option as well) – Evan Volgas Dec 24 '14 at 23:26

score 0 · Answer 3 · edited May 23 '17 at 10:33

There are really two options here. One is to union your initial query with another one that counts all tickets, something like

SELECT case

when Submission__bTracking = 'Phone' then 'Phone'
when Submission__bTracking = 'Web' then 'Web'
when Submission__bTracking = 'Email' then 'Email
when Submission__bTracking = 'Live__bTech__bSupport' then '@ Live Tech Support
when Submission__bTracking = 'Verbal' then 'Verbal Request'
when Submission__bTracking = 'Fax__b__f__bform' then 'Fax / Form'

End as Sub_Tracking,

COUNT(Submission__bTracking) as tickets FROM dbo.MASTER30

WHERE mrSUBMITDATE >= (CONVERT (date, CURRENT_TIMESTAMP -1))
AND mrSUBMITDATE < (CONVERT (date, CURRENT_TIMESTAMP))

GROUP BY Submission__bTracking

UNION ALL

SELECT "All" AS Sub_Tracking,

COUNT(Submission__bTracking) as tickets FROM dbo.MASTER30

WHERE mrSUBMITDATE >= (CONVERT (date, CURRENT_TIMESTAMP -1))
AND mrSUBMITDATE < (CONVERT (date, CURRENT_TIMESTAMP))

Another option would be to use WITH ROLLUP (eg http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html)

That would look like this:

SELECT case

when Submission__bTracking = 'Phone' then 'Phone'
when Submission__bTracking = 'Web' then 'Web'
when Submission__bTracking = 'Email' then 'Email
when Submission__bTracking = 'Live__bTech__bSupport' then '@ Live Tech Support
when Submission__bTracking = 'Verbal' then 'Verbal Request'
when Submission__bTracking = 'Fax__b__f__bform' then 'Fax / Form'

End as Sub_Tracking,

COUNT(Submission__bTracking) as tickets FROM dbo.MASTER30

WHERE mrSUBMITDATE >= (CONVERT (date, CURRENT_TIMESTAMP -1))
AND mrSUBMITDATE < (CONVERT (date, CURRENT_TIMESTAMP))

GROUP BY Submission__bTracking WITH ROLLUP

The later option won't output the "ALL" line but it is a lot shorter... also, you can't use ORDER BY and WITH ROLLUP together, so know that in advance as well.

Between the two, I'd probably use the first option.... which is the same as the two provided by the other folks who responded, except that I'd use UNION ALL instead of UNION. It's a nitpicky detail, but why bother with duplicate checks if you don't need them. For more on UNION vs UNION ALL, check out: What is the difference between UNION and UNION ALL?)

Get SUM of query result WITHIN same query

3 Answers3