CODEWITHSUNDEEP
sql-server-2008
rowmark
rowmark

Reputation:

Group by query in sql server 2008 help

I have this simple Orders table 

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

In Sql server in one query how do I get for each Orderdate: - TotalNumber of orders with orderstatus=1, - TotalNumber of orders with orderstatus=2

Thanks

Upvotes: 0

Views: 7106

Answers (3)

rowmark
rowmark

Reputation:

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

Upvotes: 0

MartW
MartW

Reputation: 12538

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.

Upvotes: 1

S.Skov
S.Skov

Reputation: 717

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..

Upvotes: 1

Related Questions