CODEWITHSUNDEEP
sqlsql-server-2008
GijsA
GijsA

Reputation: 250

Using Rollup for a specific column

I'm trying to remake this excel sheet using SQL.

Example

I'm trying to make a total line grouping the LOAN_ACCT_1 with the same numbers. Then, I want to sum all the values in the colums following LOAN_AM.

I've been trying this for quite a while now, but I came the closest with using ROLLUP. The problem that I'm having is that I can't seem to group on the LOAN_ACCT_1 only.

I came close with this solution from this microsoft site, but I can't seem to finish the solution. Is there an easier way that I'm overlooking?

SELECT CASE WHEN(GROUPING(l.LOAN_ACCT_1) = 1) THEN 'TOTAL'
            ELSE ISNULL(l.LOAN_ACCT_1, 'UNKNOWN')
            END AS LOAN_ACCT_1,
       CASE WHEN(GROUPING(l.LOAN_TYPE) = 1) THEN 'ALL'
            ELSE ISNULL(l.LOAN_TYPE, 'UNKNOWN')
            END AS LOAN_TYPE,
       SUM(l.LOAN_AMT_FINANCED) as LOAN_AMT_FINANCED
FROM LLOAN As l
GROUP BY l.LOAN_ACCT_1, LOAN_TYPE WITH ROLLUP
ORDER BY l.LOAN_ACCT_1

Upvotes: 1

Views: 1516

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If you only want the total for one column and not both, then use GROUPING_SETS:

GROUP BY GROUPING SETS ((l.LOAN_ACCT_1, LOAN_TYPE), (l.LOAN_ACCT_1))

You might want to review the documentation to better understand how this works.

Upvotes: 3

Related Questions