CODEWITHSUNDEEP
sqlsql-server-2008
acadia
acadia

Reputation: 2333

Distinct Record Issue in MS SQL Server 2008 query

I have this query to fetch the total OrderStatus that have values 1 and 5. How do I Sum only distinct OD.OrderStatus=2 as there can be multiple records in Orderdetails table with OrderStatus as 2.

Please help

SELECT O.OrderDate,
Sum(Case When OD.OrderStatus = 2 Then 1 Else 0 End) AS OrdersOffered,
Sum(Case When OD.OrderStatus = 1 Then 1 Else 0 End) AS OrdersAccepted
FROM Orders O,OrderDetails OD
Where O.Order_ID=OD.Order_ID
GROUP BY OrderDate

Upvotes: 0

Views: 529

Answers (2)

David Graça
David Graça

Reputation: 333

Just use a subquery to help with your result.

SELECT 
O.OrderDate, 
Sum(Case When OD.OrderStatus = 2 Then 1 Else 0 End) AS OrdersOffered, 
Sum(Case When OD.OrderStatus = 1 Then 1 Else 0 End) AS OrdersAccepted 
FROM Orders O inner join
    (Select distinct Order_ID,OrderStatus 
    from OrderDetails) OD on O.Order_ID=OD.Order_ID
GROUP BY OrderDate

Should do the trick...

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

So, you want it to be 1, regardless of how many you have? Maybe use SIGN?

SELECT O.OrderDate
  ,SIGN(Sum(Case When OD.OrderStatus = 2 Then 1 Else 0 End)) AS OrdersOffered
  ,Sum(Case When OD.OrderStatus = 1 Then 1 Else 0 End) AS OrdersAccepted
FROM Orders O
  JOIN OrderDetails OD  ON O.Order_ID=OD.Order_ID
GROUP BY OrderDate

Upvotes: 1

Related Questions