Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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
like image 409
Jonas Stensved Avatar asked Dec 31 '25 18:12

Jonas Stensved


1 Answers

ORDER BY
  CASE WHEN seller_name IS NULL THEN 1 ELSE 0 END,
  seller_name
like image 161
MatBailie Avatar answered Jan 02 '26 09:01

MatBailie



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!