CODEWITHSUNDEEP
sqlsql-server
Jonas Stensved
Jonas Stensved

Reputation: 15306

How do I sort results from a nested select while keeping the rollup on the last row?

How do I sort the results into the following example by the sellers name while keeping the rollup at the bottom?

Since the the grouping is applied to the nested SELECT I can't use ORDER BY and since the grouping isn't applied at the top level I can't use the GROUPING either.

Click here to see the working example in SQL Fiddle.

CREATE TABLE Sales 
(
        SellerID    INT
    ,   StoreID     INT
    ,   Price       MONEY
);

CREATE TABLE Sellers 
(
        SellerID    INT
    ,   Name        VARCHAR(50)  
)

INSERT INTO Sales VALUES 
    (1, 1, 100),
    (1, 1, 100),
    (1, 1, 100),
    (2, 2, 200),
    (2, 2, 200),
    (3, 2, 250),
    (3, 2, 250),
    (3, 2, 250),
    (3, 2, 250);

INSERT INTO Sellers VALUES
    (1, 'C. Thirdplace'),
    (2, 'A. Firstplace'),
    (3, 'B. Secondplace');

SELECT  s.Name          AS Seller_Name
    ,   x.TotalSales    AS Total_Sales
FROM 
(  
    SELECT      s.SellerID AS SellerID
            ,   SUM(s.Price) AS TotalSales
    FROM        Sales s 
    GROUP BY    s.SellerID 
    WITH ROLLUP
) x
LEFT JOIN   Sellers s 
ON          s.SellerID = x.SellerID;

Which produces the following result:

SELLER_NAME      TOTAL_SALES
---------------  -----------
C. Thirdplace        300
A. Firstplace        400
B. Secondplace      1000
(null)              1700

Upvotes: 3

Views: 131

Answers (1)

MatBailie
MatBailie

Reputation: 86765

ORDER BY
  CASE WHEN seller_name IS NULL THEN 1 ELSE 0 END,
  seller_name

Upvotes: 4

Related Questions