SQL Server Performance

Cube Function

Discussion in 'Analysis Services/Data Warehousing' started by sueolo, Dec 2, 2005.

  1. sueolo New Member

    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 TotalQty
    FROM 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
  2. Adriaan New Member

    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.
  3. cemuney New Member

    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

  4. sueolo New Member

    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

Share This Page