none
With Rollup Above Group

    Question

  • Experts - 

    Is there any way to have the summary values generated by WITH ROLLUP to be displayed on top of their respective groups rather than below?

    Thank you!


    Bonediggler

    Thursday, October 24, 2013 3:54 PM

Answers

  • CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
    INSERT Sales VALUES(1, 2005, 12000)
    INSERT Sales VALUES(1, 2006, 18000)
    INSERT Sales VALUES(1, 2007, 25000)
    INSERT Sales VALUES(2, 2005, 15000)
    INSERT Sales VALUES(2, 2006, 6000)
    INSERT Sales VALUES(3, 2006, 20000)
    INSERT Sales VALUES(3, 2007, 24000)
    
    SELECT
          CASE WHEN GROUPING(Yr) = 0
                THEN CAST (Yr AS CHAR(5))
                ELSE 'ALL'
          END AS Yr,
          SUM(Sales) AS Sales
    FROM Sales
    GROUP BY Yr WITH ROLLUP
    ORDER BY yr DESC


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Marked as answer by Bonediggler Thursday, October 24, 2013 7:35 PM
    Thursday, October 24, 2013 4:14 PM

All replies

  • Give the Grouping value a name like "_TOTAL_" or create another column say INDX 1 for details values and 0 for Grouping value and then sort.

    • Edited by Taherul673 Thursday, October 24, 2013 4:11 PM
    Thursday, October 24, 2013 4:10 PM
  • CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
    INSERT Sales VALUES(1, 2005, 12000)
    INSERT Sales VALUES(1, 2006, 18000)
    INSERT Sales VALUES(1, 2007, 25000)
    INSERT Sales VALUES(2, 2005, 15000)
    INSERT Sales VALUES(2, 2006, 6000)
    INSERT Sales VALUES(3, 2006, 20000)
    INSERT Sales VALUES(3, 2007, 24000)
    
    SELECT
          CASE WHEN GROUPING(Yr) = 0
                THEN CAST (Yr AS CHAR(5))
                ELSE 'ALL'
          END AS Yr,
          SUM(Sales) AS Sales
    FROM Sales
    GROUP BY Yr WITH ROLLUP
    ORDER BY yr DESC


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    • Marked as answer by Bonediggler Thursday, October 24, 2013 7:35 PM
    Thursday, October 24, 2013 4:14 PM
  • I think that if there are multiple groups (there are) then the sort will just go by INDX irrespective of the order of groups and associated details.


    Bonediggler

    Thursday, October 24, 2013 4:16 PM
  • I guess for each grouping and corresponding details ,you have to maintain different sorting.


    Sorry quickly snagged some example for the below link:

    http://technet.microsoft.com/en-us/library/ms189305%28v=sql.90%29.aspx


    SELECT CASE WHEN (GROUPING(Item) = 1) THEN '__GRAND TOTAL__' ELSE ISNULL(Item, 'UNKNOWN') END AS Item, CASE WHEN (GROUPING(Color) = 1) THEN '_ALL_' ELSE ISNULL(Color, 'UNKNOWN') END AS Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH ROLLUP

    ORDER BY Item,Color

    Item                 Color                QtySum                    

    -------------------- -------------------- --------------------------

    __GRAND TOTAL_       _ALL_                  658.00   WILL BE TOP MOST

    Chair                _ALL_                  311.00   WILL BE AT THE TOP               

    Chair                Blue                 101.00                    

    Chair                Red                  210.00                    

    Table                _ALL_                  347.00     ORDER WILL BE PRESERVED BY ITEM VALUE 

    Table                Blue                 124.00                    

    Table                Red                  223.00                    




    • Edited by Taherul673 Thursday, October 24, 2013 4:38 PM
    Thursday, October 24, 2013 4:33 PM
  • Hi,

    Try like this,

    DECLARE  @Sales TABLE(EmpId INT, Yr VARCHAR(20), Sales MONEY)
    INSERT @Sales VALUES(1, 2005, 12000)
    INSERT @Sales VALUES(1, 2006, 18000)
    INSERT @Sales VALUES(1, 2007, 25000)
    INSERT @Sales VALUES(2, 2005, 15000)
    INSERT @Sales VALUES(2, 2006, 6000)
    INSERT @Sales VALUES(3, 2006, 20000)
    INSERT @Sales VALUES(3, 2007, 24000)
    
    SELECT
           ISNULL(Yr,'Total') Total,
          SUM(Sales) AS Sales
    FROM @Sales
    GROUP BY Yr WITH ROLLUP
    ORDER BY Yr


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Thursday, October 24, 2013 4:43 PM
  • Easiest way is to use the GROUPING() function in the order by.  For example

    Declare @Test Table(Company int, Division int, SalesPersionID int, OrderID int, SaleAmt int);
    Insert @Test(Company, Division, SalesPersionID, OrderID, SaleAmt) Values 
    (1,1,1,1,220),
    (1,1,1,2,100),
    (1,1,3,3,10),
    (1,4,7,4,5),
    (2,1,8,5,15),
    (2,1,9,6,1);
    Select t.Company, t.Division, t.SalesPersionID, SUM(t.SaleAmt) As Sales
    From @Test t
    Group By t.Company, t.Division, t.SalesPersionID With Rollup
    Order By Grouping(t.Company) Desc, t.Company, 
       Grouping(t.Division) Desc, t.Division, 
       Grouping(t.SalesPersionID) Desc, SalesPersionID

    Tom

    Thursday, October 24, 2013 4:49 PM