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