How to sort the results from a nested selection, while maintaining the convolution in the last row?

How to sort the results in the following example by the seller’s name, keeping the summary below?

Since grouping applies to nested SELECT, I cannot use ORDER BY, and since grouping does not apply at the top level, I cannot use GROUPING .

Click here to see a 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 gives the following result:

SELLER_NAME      TOTAL_SALES
---------------  -----------
C. Thirdplace        300
A. Firstplace        400
B. Secondplace      1000
(null)              1700
+3
source share
1 answer
ORDER BY
  CASE WHEN seller_name IS NULL THEN 1 ELSE 0 END,
  seller_name
+4
source

All Articles