SQL Server query GROUPING with ROLLUP

Question

I am trying to run a query with a ROLLUP function.

Assume that my query is:

SELECT  GEN.orgId,GEN.locId,GEN.rvcId
GEN.rvcName,GEN.menuItemID,GEN.menuItemName , SUM(salesTotal) as
salesTotal FROM GEN  GROUP BY  GEN.orgId,GEN.locId,
GEN.rvcName,GEN.menuItemID,GEN.menuItemName  ORDER BY GEN.menuItemName

What this query outputs is:

orgId  locId  rvcName          menuItemID    menuItemName        salesTotal
10200  10002  Sydney Retail    10870         Cho Milk Shake        7.500000
10200  10002  Sydney Wholesale 10870         Cho Milk Shake        7.500000
10200  10002  Sydney Retail    10850         Berry Milk Shake     10.920000
10200  10002  Sydney Wholesale 10850         Berry Milk Shake     10.920000
10200  10002  Sydney Retail    10898         Apple Custard         5.460000
10200  10002  Sydney Wholesale 10898         Apple Custard         5.460000

When I apply GROUPING and ROLLUP to my query:

SELECT GEN.orgId,GEN.locId,  GEN.menuItemID, GEN.menuItemName,
CASE WHEN GROUPING( GEN.rvcName) = 1
    THEN 'Grand Total'
    ELSE GEN.rvcName END as rvcName
,SUM(salesTotal) as salesTotal
GROUP BY GEN.orgId,GEN.locId, GEN.rvcId, GEN.rvcName, GEN.menuItemID, GEN.menuItemName WITH ROLLUP  
ORDER BY   GEN.menuItemID asc   

This query outputs (for Berry Milk Shake only):

orgId   locId   menuItemID  menuItemName    rvcName           salesTotal
10200   10002   NULL            NULL             Sydney Retail     10.920000
10200   10002   NULL            NULL             Grand Total       10.920000
10200   10002   NULL            NULL             Sydney Wholesale  10.920000
10200   10002   NULL            NULL             Grand Total       10.920000
10200   10002   NULL            NULL             Grand Total       21.840000
10200   NULL    NULL            NULL             Grand Total       21.840000
NULL    NULL    NULL            NULL             Grand Total       21.840000
10200   10002   10850           Berry Milk Shake Sydney Wholesale  10.920000
10200   10002   10850           NULL             Sydney Wholesale  10.920000
10200   10002   10850           Berry Milk Shake Sydney Retail     10.920000
10200   10002   10850           NULL             Sydney Retail     10.920000

My Question is: I have got so many unnecessary data created with this query. How can I refine the query and produce something like below. Thanks in advance.

What I want to achieve is:

orgId  locId  rvcName           menuItemID    menuItemName          salesTotal
10200  10002  Sydney Retail     10870         Cho Milk Shake        7.500000
10200  10002  Sydney Wholesale  10870         Cho Milk Shake        7.500000
10200  10002  ALL               10870         Cho Milk Shake       15.000000
10200  10002  Sydney Retail     10850         Berry Milk Shake     10.920000
10200  10002  Sydney Wholesale  10850         Berry Milk Shake     10.920000
10200  10002  ALL               10850         Berry Milk Shake     21.840000
10200  10002  Sydney Retail     10898         Apple Custard         5.460000
10200  10002  Sydney Wholesale  10898         Apple Custard         5.460000
10200  10002  ALL               10898         Apple Custard        10.920000

Answer 1

Thanks again for this question.. One thing I would like to add here is, we can not get the same result as you are expecting here is. The reason behind that is, rvcName column data wont come in same way as you want. When we use group by function, all the column data, which we have included in our query is grouped together.

So, all Sydney Retail, Sydney Wholesale will ge grouped together. This will surely get the result near by as you want. If you see in your expected answer, you have grouped data of all columns except rvcName.

so, here is your query, I hope this may guide you...

select orgid,locid,rvcname, menuitemname,menuitemid, sum(salesTotal) as 'total sale' from gen group by orgid,locid, rvcName,menuItemName, menuitemid with rollup

and this will give you result like this

orgid locid rvcname menuitemname menuitemid total sale
1 2 sydney retail apple custard 30 5.46
1 2 sydney retail apple custard NULL 5.46
1 2 sydney retail berry shake 20 10.92
1 2 sydney retail berry shake NULL 10.92
1 2 sydney retail milk shake 10 7.5
1 2 sydney retail milk shake NULL 7.5
1 2 sydney retail NULL NULL 23.88
1 2 syndney wholesale apple custard 30 5.46
1 2 syndney wholesale apple custard NULL 5.46
1 2 syndney wholesale berry shake 20 10.92
1 2 syndney wholesale berry shake NULL 10.92
1 2 syndney wholesale milk shake 10 7.5
1 2 syndney wholesale milk shake NULL 7.5
1 2 syndney wholesale NULL NULL 23.88
1 2 NULL NULL NULL 47.76
1 NULL NULL NULL NULL 47.76
NULL NULL NULL NULL NULL 47.76

Hope you got the problem area...only thing is, when we use any group function/ aggregate function, all similar data gets grouped together.

If the result text looks messy, try to paste in excel.. I dnt knw why though i copied result from sql server to excel, it appears messy.. apologies if inconvinience.

If still you get some doubt, feel free to let me know.

Kind regards, Ashay (Mumbai / India)