N
N
nordwind20132018-08-08 15:38:29
SQL
nordwind2013, 2018-08-08 15:38:29

How to print the result of cube in the correct order?

There is such a request

SELECT 
  ISNULL(cast(Seller as varchar(30)), 
        case when GROUPING(Seller)=1
           then 'ALL' end) as Seller,
  ISNULL(cast(ContactName as varchar(30)), 
        case when GROUPING(ContactName)=1
           then 'ALL' end) as Cust,
  COUNT(*) AS Ammount 
  
FROM (SELECT EmployeeID,CONCAT(FirstName,' ',LastName) AS Seller 
FROM Northwind.Employees) A LEFT JOIN 
(SELECT EmployeeID,CustomerID 
FROM Northwind.Orders
WHERE YEAR(OrderDate)=1998 
GROUP BY EmployeeID,CustomerID) B 
ON A.EmployeeID=B.EmployeeID LEFT JOIN 
(SELECT ContactName,CustomerID FROM Northwind.Customers) C 
ON C.CustomerID = B.CustomerID
GROUP BY cube(ContactName,Seller)
ORDER BY ContactName,Seller

Now it outputs First ALL|ALL|Amount,
Next - Name|ALL|Amount,
and then it goes like this _4ovtG4-5k8.jpg
And this is how it should be

Seller Customer Amount
ALL ALL <total number of sales>
<name> ALL <number of sales for this seller>
ALL <name> <number of sales for this customer>
<name> <name> <number of sales for this seller for this customer>

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question