Sub total at the end of Query result Table

Question

I'm trying get the subtotal at the end of the table for Accepted, cancelled, noShow and flagged...I try grouping but its throw an error. Is there any other or easy way to do it?

Select * From (
SELECT 
c.ComID, ISNULL(c.Client,'-') AS Driver_Name, GROUPING(c.MTNum) WHILE 1 THEN 'TOTAL' ELSE ISNULL(c.MTNum,'-'), ISNULL(Accepted,0) AS Accepted, ISNULL(Cancelled,0) AS Cancelled, ISNULL(NoShow,0) AS No_Show, ISNULL(Flagged,0) AS Flagged
FROM
(SELECT 
    HistoryLTD.CompanyID, HistoryLTD.Client, HistoryLTD.MTNum
FROM
    HistoryLTD
GROUP BY HistoryLTD.Client, HistoryLTD.CompanyID, HistoryLTD.MTNum) c
    LEFT JOIN
(SELECT 
    HistoryLTD.MTNum, count(HistoryLTD.MTNum) AS Cancelled
FROM
    HistoryLTD
WHERE
    HistoryLTD.Notes LIKE '%Cancelled%' AND TimeAssigned > '2011-08-28' AND TimeAssigned < '2011-08-30'
GROUP BY HistoryLTD.MTNum) c1 ON c.MTNum = c1.MTNum
    LEFT JOIN
(SELECT 
    HistoryLTD.MTNum, count(HistoryLTD.MTNum) AS NoShow
FROM
    HistoryLTD
WHERE
    HistoryLTD.Notes LIKE '%No Show%' AND TimeAssigned > '2011-08-28' AND TimeAssigned < '2011-08-30'
GROUP BY HistoryLTD.MTNum) c2 ON c.MTNum = c2.MTNum
    LEFT JOIN
(SELECT 
    HistoryLTD.MTNum, count(HistoryLTD.MTNum) AS Flagged
FROM
    HistoryLTD
WHERE
    HistoryLTD.Notes LIKE '%Flagged%' AND TimeAssigned > '2011-08-28' AND TimeAssigned < '2011-08-30'
GROUP BY HistoryLTD.MTNum) c3 ON c.MTNum = c3.MTNum
LEFT JOIN
(SELECT 
    HistoryLTD.MTNum, count(HistoryLTD.MTNum) AS Accepted
FROM
    HistoryLTD
where 
    Notes Not LIKE '%Cancelled%'
    OR Notes Not LIKE '%No Show%'
    OR Notes Not LIKE '%Flagged%'
    AND TimeAssigned > '2011-08-28' AND TimeAssigned < '2011-08-30'
GROUP BY HistoryLTD.MTNum) c4 ON c.MTNum = c4.MTNum ) firstQuery
UNION
Select Null, Null, 'Total', SUM(Accepted), SUM(Cancelled), SUM(No_Show), SUM(Flagged)
  FROM ???

Result I'm Looking for...

ComID   Client_Name mtNum   acc canc    noshow  Flgd
CTBV    Brian       3834    19  0   5   1
CTBV    Central     3863    5   0   0   5
CTBV    Charles     3815    25  0   2   5
CTBV    Chris       3842    14  0   0   0
CTBV    Chris       3864    17  0   1   0
CTBV    Cory        3829    15  0   1   1
               TOTAL        95  0   9   12   <-- This total at bottom

I wrote "GROUPING(c.MTNum) WHILE 1 THEN 'TOTAL' ELSE ISNULL(c.MTNum,'-') " and It says "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'c'. Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'." — DBA_assis, Aug 30 '12 at 17:41
I don't see where you wrote it and I still don't understand on which line the error was thrown. Line numbers don't help. Second, you clearly don't understand what's the meaning of `LEFT JOIN` if you try to use it in order to `sum`. read: http://www.techonthenet.com/sql/sum.php — Nir Alfasi, Aug 30 '12 at 17:48

score 1 · Accepted Answer · answered Aug 30 '12 at 17:47

1

Use UNION to combine two queries, first query would show the aggregate by group, second query would show the total
To make sure the total is in the bottom, sort by an expression similar to this:

Order by Case ComID when NULL then 'ZZZZZZ' else ComID end

answered Aug 30 '12 at 17:47

Sam Anwar

669
5
8

Im not able to union whole query with other table... Because I used Alias for Accept, cancelled, flagged and noshoq – DBA_assis Aug 30 '12 at 19:57
I should not matter. Wrap another select around your existing complex query, and then union the result. Include the same number of columns in the bottom (union) query and just fill in with NULL. – Sam Anwar Aug 30 '12 at 21:37
Select * from ( << your existing query here>> ) Union <> – Sam Anwar Aug 30 '12 at 21:38
Hey Sam, I just edit the query as per your suggestion, Is that what you want me to do? because its gives me an error on "Incorrect syntax near the keyword 'Union'." And in sum(accepted), accepted not found. Pls help on this. Thanks. Im just about to finish this task. – DBA_assis Aug 31 '12 at 16:17
Any possibility you can send me the creation script for the tables referenced by your query? I can then create them on my server and troubleshoot the query for you. – Sam Anwar Aug 31 '12 at 16:53
I just edited your query to fix the first error. Let me know what you get now. – Sam Anwar Aug 31 '12 at 16:56
Its company data, so I have limitation to send you real data or table structure. but give me a moment i'll try to send you from other source. – DBA_assis Aug 31 '12 at 17:18
And about your edited query, Im sorry I cant see any changes at my side. Can you save it again. Thanks. – DBA_assis Aug 31 '12 at 17:19
Hey sam You can view table structure on this link. Hope this will help you. http://pastebin.com/embed_iframe.php?i=mLjRedNC – DBA_assis Aug 31 '12 at 17:37
If I add alias to the query it is giving me this error - The multi-part identifier "firstquery.Accepted" could not be bound. – DBA_assis Aug 31 '12 at 18:28
I am getting this error when I click on the link: ERROR, PASTE ID IS INVALID, OR PASTE HAS BEEN REMOVED! – Sam Anwar Aug 31 '12 at 18:47
Oh Its because its expired after an Hour of my post. but hey I solve the issue with your help. Thanks a lot for your valuable time. New in this database field so, need seniors guidance. thanks – DBA_assis Aug 31 '12 at 19:31

Sub total at the end of Query result Table

1 Answers1