Distinct Record Issue in MS SQL Server 2008 query

Question

Hello, 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

Answer 1

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

Answer 2

Rob Farley, Thanks but that is not exactly what I want. If there are 10 orders it should show 10 and not 1.

The sum I am doing is for all the orders in a day and not just one order.

Thanks again