Group by query in sql server 2008 help

Question

Hello, I have this simple Orders table

Orders
    -------------
    OrderID
    OrderDesc
    OrderDate
    OrderStatus
    OrderAmount

In Sql server in one query How do I for each date TotalNumber of orders with orderstatus=1,TotalNumber of orders with orderstatus=2 Group by Orderdate.

Thanks

Answer 1

Not tested or anything but have you tried just selecting the count from your groups?

Something like:

SELECT Count(OrderStatus) As Amount, OrderDate
FROM Orders
GROUP BY OrderStatus, OrderDate
HAVING OrderStatus = 2 OR OrderStatus = 1

Not sure about the syntax though or if HAVING is used in SQL Server..

Answer 2

SELECT OrderDate,
Sum(Case When OrderStatus = 1 Then 1 Else 0 End) AS TotalOrderStatusOne,
Sum(Case When OrderStatus = 2 Then 1 Else 0 End) AS TotalOrderStatusTwo
FRMO Orders
GROUP BY OrderDate

will give you what you want if you want it on one row per OrderDate. Gets messy if you have too many values for OrderStatus, though.

Answer 3

Sorry I am looking for something like this:

OrderDate Records with OrderStatus =1 Records With OrderStatus =2 1-Aug-09 10 20 2-Aug-09 20 10

Thanks