SQL Server Performance Forum – Threads Archive
Cube Function
Hi, all. I have been experimenting with the CUBE function, but have only been able to generate a valid result set with the following very basic query using the Northwind database that comes with SQL Server 2000: SELECT ProductName, Sum(Quantity) AS TotalQtyFROM Products INNER JOIN [Order Details]
On Products.ProductID = [Order Details].ProductID
GROUP BY ProductName
WITH CUBE
ORDER BY ProductName I’d like to extend this query to include a column for the unit price and a total product sales column (TotalQty * unit price). The UnitPrice column exists in both the Products and Order Details tables. Here is one of my unsuccessful attempts: SELECT ProductName, Sum([Order Details].Quantity) AS TotalQty, [Order Details].UnitPrice * (Sum([Order Details].Quantity)) AS TotalProductSale
FROM Products INNER JOIN [Order Details]
On Products.ProductID = [Order Details].ProductID
GROUP BY ProductName, [Order Details].Quantity, [Order Details].UnitPrice
WITH CUBE
ORDER BY ProductName, [Order Details].Quantity The above gives me a bunch of records with NULL in the ProductName column and multiple records for each Product. If anyone can provide any insight, thanks in advance! Thanks, Sue
You will see a row for each existing combination of values on the columns identified in the GROUP BY clause. I suggest that you drop the WITH CUBE option first, and experiment with the grouping and calculations first. If you have them right, you add the WITH CUBE option again. The rows with NULL on the ProductName are rows generated by the WITH CUBE option.
is it what u want??? SELECT ProductName , Sum([Order Details].Quantity) AS TotalQty,
(Sum([Order Details].UnitPrice*[Order Details].Quantity)) AS TotalProductSale
FROM Products left outer JOIN [Order Details]
On Products.ProductID = [Order Details].ProductID
GROUP BY ProductName
WITH CUBE
ORDER BY ProductName
Hi, Adriann and Cemuney. Thank you for your replies. I see what is happening now. The result set containing rows where the Product Name is NULL are not errors, but rather an indication of how the CUBE function works. The data is still valid. Thanks for the explanation Adriaan. Cemuney, I used the query you provided (changed it to an INNER JOIN) and it works just fine. This is a good example for me to build on. I guess I was hoping for a "cleaner" result set without the extra rows, but that’s not now the cube function works. Thank you for your help. – Sue
]]>