CODEWITHSUNDEEP
sqlsum
Sung
Sung

Reputation: 209

Get SUM of query result WITHIN same query

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!!

Upvotes: 1

Views: 71

Answers (3)

buhtla
buhtla

Reputation: 2909

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))

Upvotes: 2

Evan Volgas
Evan Volgas

Reputation: 2911

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?)

Upvotes: 0

Paul Abbott
Paul Abbott

Reputation: 7211

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))

Upvotes: 1

Related Questions