Mssql summary row using ROLLUP

Question

I'm trying to generate a summary row using ROLLUP grouping,
Here is my query

 SELECT nic as NIC,branch_id,SUM(as_share),SUM(as_deposit) as as_deposit,SUM(as_credits) as as_credits,SUM(as_fixed) as as_fixed,SUM(as_ira) as as_ira,SUM(as_saviya) as as_saviya
    FROM As_Member_Account_Details
    GROUP BY nic,branch_id
    WITH ROLLUP

But it give me this output,

112233  1       30.00   0.00    0.00    50.00   0.00    0.00
112233  2       20.00   0.00    0.00    0.00    0.00    0.00
112233  3       0.00    0.00    0.00    0.00    0.00    0.00
112233  NULL    50.00   0.00    0.00    50.00   0.00    0.00
NULL    NULL    50.00   0.00    0.00    50.00   0.00    0.00

The row before the last row is unnecessary. Because there should be only 3 data rows + a summary row. How can I eliminate that row

rollup generates row for evey grouped column. If you don't want these columns, you can insert this data to temp table and then delete unnecessary rows. — Burcin, Aug 24 '17 at 13:11
Please post DDL,DML Of the tables involved and dont post images.Having this info helps others to quickly repro your issue and asnwer better.Below is some sample which might help you understand. **--sample data** `create table t1 ( id int ) insert into t1 values (1), (2), (1) ` **my current query/what i have tried:** `select id,count(*) as cnt from t1 group by id` **my current result:** `id cnt 1 2 2 1` **my expected result:** `id cnt 1 2 2 1 1 2` — TheGameiswar, Aug 24 '17 at 13:11

score 0 · Answer 1 · answered Aug 24 '17 at 13:18

WITH CTE_YourQuery AS
(
    SELECT nic as NIC,branch_id,SUM(as_share),SUM(as_deposit) as as_deposit,SUM(as_credits) as as_credits,SUM(as_fixed) as as_fixed,SUM(as_ira) as as_ira,SUM(as_saviya) as as_saviya
    FROM As_Member_Account_Details
    GROUP BY nic,branch_id
    WITH ROLLUP
)
SELECT * 
FROM CTE_YourQuery
WHERE NOT (nic IS NOT NULL AND branch_id IS NULL)

xQbert · Accepted Answer · 2017-08-24T13:29:54.457

0

Grouping sets allows more granular control when cubeing data.

SELECT nic as NIC
     , branch_id,SUM(as_share)
     , SUM(as_deposit) as as_deposit
     , SUM(as_credits) as as_credits
     , SUM(as_fixed) as as_fixed
     , SUM(as_ira) as as_ira
     , SUM(as_saviya) as as_saviya
FROM As_Member_Account_Details
GROUP BY GROUPING SETS ((nic,branch_id),())

edited Aug 24 '17 at 13:29

answered Aug 24 '17 at 13:23

xQbert

34,733
2
41
62

Mssql summary row using ROLLUP

2 Answers2