Aggregate sum gives wrong results
-
Hi,
I think my aggregate sum function is doing something wrong in the following query
SELECT SUM(Order_details.OrderPrice) AS OrderTotal,
MIN(Products.Manufacturer + ' ' + Products.ProductName) AS ProductNameFull2
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
GROUP BY Products.Manufacturer + ' ' + Products.ProductName
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'Dell COMPUTER')
When I compare the Ordertotal I get using the above query with the ordertotal I calculate using excel from the following query which does not sum there is a very big difference.
SELECT Products.Manufacturer + ' ' + Products.ProductName AS ProductNameFull2, Orders.OrderID, Orders.OrderDate, Order_details.OrderPrice, Order_details.Quantity,
Orders.Deleted
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'Dell COMPUTER')
ORDER BY Orders.OrderDate DESC
I cannot understand what am I doing wrong in my first query because I checked and the second query is giving correct results.
Can someone please help to get the sum in a single query. I can use some other query but I just need the total when I group by Products.Manufacturer + ' ' + Products.ProductName.
Thanks.
-
Change
HAVING to a WHERE Clause!
Like
SELECT SUM(Order_details.OrderPrice) AS OrderTotal,
MIN(Products.Manufacturer + ' ' + Products.ProductName) AS ProductNameFull2
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE()) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'Dell COMPUTER')
GROUP BY Products.Manufacturer + ' ' + Products.ProductName
WHERE Filters Before the aggregation HAVING does it afterwards
HTH
* Noel -
when i try using the above statement it gives me this error
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'GROUP'.
-
Just a missing parenthesis
SELECT SUM(Order_details.OrderPrice) AS OrderTotal,
MIN(Products.Manufacturer + ' ' + Products.ProductName) AS ProductNameFull2
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'Dell COMPUTER')
GROUP BY Products.Manufacturer + ' ' + Products.ProductName
HTH
* Noel -
That works. Thanks a lot.
Also I wanted to know if you could use 2 cases in the case clause. Like i have the following case
(CASE WHEN Max(SalePrice) > 0 THEN Max(SalePrice) ELSE Max(UnitPrice) END)
in this query
SELECT Max(Manufacturer + ' ' + ProductName) As ProductNameFull, ((CASE WHEN Max(SalePrice) > 0 THEN Max(SalePrice) ELSE Max(UnitPrice) END) - Max(cost)) / (CASE WHEN Max(cost) > 0 THEN Max(cost) ELSE 1 END) AS margin INTO #SeqNumberTemp FROM Products Where Products.NotOnWeb = 0 GROUP BY Manufacturer + ' ' + ProductName
I want to change it to
(CASE WHEN Max(SalePrice) > 0 THEN Max(SalePrice) ELSEIF Max(UnitPrice) > 0 Then Max(UnitPrice) Else 1 END)
-
CASE WHEN Max(SalePrice) > 0 THEN Max(SalePrice) ELSE CASE WHEN Max(UnitPrice) > 0 Then Max(UnitPrice) Else 1 END END
-
Cool this also works great. Thanks again for your help. I really appreciate it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply