How do I do a ROLLUP on Language column and get a row of TOTAL as the last row?

Question

I want to do a ROLLUP on the Language column and generate the last row as a Total.
I am not sure where to put the ROLLUP.
For Eg:

AS
        BEGIN
            declare @sqlCommand NVARCHAR(4000) =
            'SELECT Language, col2, col3, col4, Total = col2 + col3 + col4
            FROM (  SELECT xL.lN, xS.sN, UC.UC
                    FROM (UC LEFT JOIN xL ON UC.Language = xL.xL) LEFT JOIN xS
                    ON UC.ssn = xS.ssn
                    WHERE
                        ((DS BETWEEN .... AND ....) AND
                        (HS BETWEEN .... AND ....) AND
                        (MS BETWEEN .... AND ....))) UCs
                        PIVOT (SUM (UC) for sN IN
                        (col2, col3, col4))
                        AS PVT;';
        EXEC SP_EXECUTESQL @sqlCommand
        END

score 0 · Answer 1 · edited Aug 16 '17 at 18:43

0

Rollup is done with Group by.. if you have to apply a group by here, you will need to group by all columns which you have selected.

edited Aug 16 '17 at 18:43

Juri Noga

4,363
7
38
51

answered Aug 16 '17 at 15:49

HappieFeet

48
8

score 0 · Answer 2 · answered Aug 16 '17 at 18:35

0

You can try grouping sets.

With result as (
    SELECT Language, col2, col3, col4, Total = col2 + col3 + col4
    FROM (  SELECT xL.lN, xS.sN, UC.UC
            FROM (UC LEFT JOIN xL ON UC.Language = xL.xL) LEFT JOIN xS
            ON UC.ssn = xS.ssn
            WHERE
            ((DS BETWEEN .... AND ....) AND
            (HS BETWEEN .... AND ....) AND
            (MS BETWEEN .... AND ....))
    ) UCs
    PIVOT (SUM (UC) for sN 
        IN (col2, col3, col4)
    ) AS PVT
)
SELECT Language, col2, col3, col4, sum(Total) as Total
FROM result
GROUP BY GROUPING SETS((Language, col2, col3, col4), ())

answered Aug 16 '17 at 18:35

S.Yang

106
3

Thanks for that. This solution helped but still, I am getting the last row as NULL. It adds a row – Jeet Patel Aug 17 '17 at 14:47
@syang Still, I am getting the last row as NULL. RANGE = [2], [3], [4], [5] SELECT Server, ' + RANGE + ', sum(Total) as Total FROM result GROUP BY GROUPING SETS((Server, ' + RANGE + '), ());'; Can you please help me with this further? – Jeet Patel Aug 17 '17 at 15:02
I am not clear on what you mean. Can you put a sample result set with the idea output for the last row? – S.Yang Aug 17 '17 at 17:03

score 0 · Answer 3 · answered Aug 17 '17 at 18:37

If you are trying to get row total with a sum of each column, you can try the following queries:

--Method 1:

With result as (
    SELECT Language, col2, col3, col4, Total = col2 + col3 + col4
    FROM (  SELECT xL.lN, xS.sN, UC.UC
            FROM (UC LEFT JOIN xL ON UC.Language = xL.xL) LEFT JOIN xS
            ON UC.ssn = xS.ssn
            WHERE
            ((DS BETWEEN .... AND ....) AND
            (HS BETWEEN .... AND ....) AND
            (MS BETWEEN .... AND ....))
    ) UCs
    PIVOT (SUM (UC) for sN 
        IN (col2, col3, col4)
    ) AS PVT
)
SELECT Language, col2, col3, col4, sum(Total) as Total
FROM result
UNION ALL
SELECT 'TOTAL', sum(col2) as col2, sum(col3) as col3, cum(col4) as col4, sum(Total) as Total
FROM result

--Method 2:

With result as (
    SELECT Language, col2, col3, col4, Total = col2 + col3 + col4
    FROM (  SELECT xL.lN, xS.sN, UC.UC
            FROM (UC LEFT JOIN xL ON UC.Language = xL.xL) LEFT JOIN xS
            ON UC.ssn = xS.ssn
            WHERE
            ((DS BETWEEN .... AND ....) AND
            (HS BETWEEN .... AND ....) AND
            (MS BETWEEN .... AND ....))
    ) UCs
    PIVOT (SUM (UC) for sN 
        IN (col2, col3, col4)
    ) AS PVT
)
SELECT case when grouping(Language) = 1 then 'Total' else Language end as Language
, sum(col2) as col2
, sum(col3) as col3
, sum(col4) as col4
, sum(Total) as Total
from result
group by Language with Rollup
order by grouping(Language)

How do I do a ROLLUP on Language column and get a row of TOTAL as the last row?

3 Answers3