ROLLUP only sums up distinct values in SQL Server 2008

Question

SELECT     
   ISNULL(Customer.Name, ' GRAND') AS CustomerName, 
   ISNULL(Item.ItemName, 'TOTAL') AS ItemName, 
   SUM(Item_Order.PriceAtDate) AS Price
FROM         
   Item_Order 
INNER JOIN
   Item ON Item_Order.ItemID = Item.ItemID 
INNER JOIN
   TheOrder ON Item_Order.OrderID = TheOrder.OrderID 
INNER JOIN
   Customer ON TheOrder.CustomerID = Customer.CustomerID
GROUP BY 
   Customer.Name, Item.ItemName, Item_Order.PriceAtDate WITH ROLLUP
ORDER BY 
   Customer.Name

Results:

CustomerName ItemName            Price
------------ ------------------- -------
GRAND        TOTAL               1380.46
Adrian       Hammer                21.88
Adrian       Hammer                21.88
Adrian       Soldering Iron        30.54
Adrian       Soldering Iron        30.54
Adrian       TOTAL                 52.42
Baker        Valve                 21.88
Baker        Valve                 21.88
Baker        TOTAL                 21.88

As you can see the TOTAL given using rollup adds up 30.54 and 21.88 which comes to 52.42 but both items are ordered twice. So I'd need the actual total, not just for distinct values.

Does anyone know what am I doing wrong?

Answer 1

I believe that doing a group by on the price column(Item_Order.PriceAtDate) that you are doing sum on is causing the problem:

Customer.Name, Item.ItemName, Item_Order.PriceAtDate WITH ROLLUP

Try to use a subquery instead

SELECT
    ISNULL(CustomerName, ' GRAND') AS CustomerName, 
    ISNULL(ItemName, 'TOTAL') AS ItemName, 
    Sum(Price) AS Price
FROM
(
SELECT     
   Customer.Name AS CustomerName,
   Item.ItemName AS ItemName,   
   Item_Order.PriceAtDate AS Price
FROM         
   Item_Order 
INNER JOIN
   Item ON Item_Order.ItemID = Item.ItemID 
INNER JOIN
   TheOrder ON Item_Order.OrderID = TheOrder.OrderID 
INNER JOIN
   Customer ON TheOrder.CustomerID = Customer.CustomerID
) as Test
GROUP BY 
   CustomerName, ItemName WITH ROLLUP
ORDER BY 
   CustomerName