CODEWITHSUNDEEP
sqlsql-servert-sql
Nick
Nick

Reputation: 372

Add Totals row that sums a specific column

I have the following query in a MS SQL Server 2014 environment and I would like to add a "GRAND_TOTALS" row at the bottom that sums the values in the aggregate (COUNT) column (Total_Exams). Ideally looking for something relatively clean, without having to use temp tables if possible. Thanks!

SELECT 

A.TEST_TYPE,(CONVERT(CHAR(10),A.TEST_DT,121)), B.UNIT, COUNT(*) AS 
'Total_Tests'

FROM USERSCT B, SEARCHPARAM B1, USERTEST A, CUSTOMEXAM C

WHERE ( B.USERID = B1.USERID
AND B.USRACCT = B1.USRACCT
AND ( B.ASOFDATE =
    (SELECT MAX(B_ED.ASOFDATE) FROM USERSCT B_ED
    WHERE B.USERID= B_ED.USERID
      AND B.USRACCT = B_ED.USRACCT
      AND B_ED.ASOFDATE <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.ASOFTIME =
    (SELECT MAX(B_ES.ASOFTIME) FROM PS_EMPLOYEES B_ES
    WHERE B.USERID = B_ES.USERID
      AND B.USRACCT = B_ES.USRACCT
      AND B.ASOFDATE = B_ES.ASOFDATE)
 AND B.USERID = A.USERID
 AND A.TEST_TYPE = 'CHISQUARE'
 AND A.USERID = C.USERID

 GROUP BY  A.TEST_TYPE,  A.TEST_DT,  B.UNIT

Current sample results:

enter image description here

Desired Results:

enter image description here

Upvotes: 0

Views: 64

Answers (1)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

Usin CTE

WITH cte AS
(
SELECT 

Cast(A.TEST_TYPE as Varchar) as TEST_TYPE, cast((CONVERT(CHAR(10),A.TEST_DT,121)) as varchar) as Date_Col, cast(B.UNIT as varchar) AS UNIT, COUNT(*) AS 
'Total_Tests'

FROM USERSCT B, SEARCHPARAM B1, USERTEST A, CUSTOMEXAM C

WHERE ( B.USERID = B1.USERID
AND B.USRACCT = B1.USRACCT
AND ( B.ASOFDATE =
    (SELECT MAX(B_ED.ASOFDATE) FROM USERSCT B_ED
    WHERE B.USERID= B_ED.USERID
      AND B.USRACCT = B_ED.USRACCT
      AND B_ED.ASOFDATE <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.ASOFTIME =
    (SELECT MAX(B_ES.ASOFTIME) FROM PS_EMPLOYEES B_ES
    WHERE B.USERID = B_ES.USERID
      AND B.USRACCT = B_ES.USRACCT
      AND B.ASOFDATE = B_ES.ASOFDATE)
 AND B.USERID = A.USERID
 AND A.TEST_TYPE = 'CHISQUARE'
 AND A.USERID = C.USERID

 GROUP BY  A.TEST_TYPE,  A.TEST_DT,  B.UNIT
)
SELECT * FROM cte
UNION
SELECT 'Grand Total','','',SUM(Total_Tests)
FROM cte

OR USING NESTED QUERY

Select * from(
SELECT 

Cast(A.TEST_TYPE as Varchar) as TEST_TYPE,cast((CONVERT(CHAR(10),A.TEST_DT,121)) as varchar) as Date_Col, cast(B.UNIT as varchar) UNIT, COUNT(*) AS 
'Total_Tests'

FROM USERSCT B, SEARCHPARAM B1, USERTEST A, CUSTOMEXAM C

WHERE ( B.USERID = B1.USERID
AND B.USRACCT = B1.USRACCT
AND ( B.ASOFDATE =
    (SELECT MAX(B_ED.ASOFDATE) FROM USERSCT B_ED
    WHERE B.USERID= B_ED.USERID
      AND B.USRACCT = B_ED.USRACCT
      AND B_ED.ASOFDATE <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.ASOFTIME =
    (SELECT MAX(B_ES.ASOFTIME) FROM PS_EMPLOYEES B_ES
    WHERE B.USERID = B_ES.USERID
      AND B.USRACCT = B_ES.USRACCT
      AND B.ASOFDATE = B_ES.ASOFDATE)
 AND B.USERID = A.USERID
 AND A.TEST_TYPE = 'CHISQUARE'
 AND A.USERID = C.USERID

 GROUP BY  A.TEST_TYPE,  A.TEST_DT,  B.UNIT
) as t1

UNION

SELECT 'Grand Total','','',SUM(Total_Tests)
FROM (SELECT 

Cast(A.TEST_TYPE as Varchar) as TEST_TYPE,cast((CONVERT(CHAR(10),A.TEST_DT,121)) as varchar) as Date_Col, cast(B.UNIT as varchar) UNIT, COUNT(*) AS 
'Total_Tests'

FROM USERSCT B, SEARCHPARAM B1, USERTEST A, CUSTOMEXAM C

WHERE ( B.USERID = B1.USERID
AND B.USRACCT = B1.USRACCT
AND ( B.ASOFDATE =
    (SELECT MAX(B_ED.ASOFDATE) FROM USERSCT B_ED
    WHERE B.USERID= B_ED.USERID
      AND B.USRACCT = B_ED.USRACCT
      AND B_ED.ASOFDATE <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
AND B.ASOFTIME =
    (SELECT MAX(B_ES.ASOFTIME) FROM PS_EMPLOYEES B_ES
    WHERE B.USERID = B_ES.USERID
      AND B.USRACCT = B_ES.USRACCT
      AND B.ASOFDATE = B_ES.ASOFDATE)
 AND B.USERID = A.USERID
 AND A.TEST_TYPE = 'CHISQUARE'
 AND A.USERID = C.USERID

 GROUP BY  A.TEST_TYPE,  A.TEST_DT,  B.UNIT
)as t2

Upvotes: 1

Related Questions