CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
tijuthomas
tijuthomas

Reputation: 57

How to group filtered rows against non filtered by month

I am trying to generate a report for accepted orders each month against the total orders for that month. For example, I have a table Orders like so:

Order_Id    Submit_Date    Order_Status
--------    -----------    ------------
1           20130501       Accepted
2           20130509       Rejected
3           20130610       Accepted
4           20130614       Accepted
5           20130626       Rejected
6           20130802       Accepted
7           20130801       Accepted
8           20131014       Accepted
9           20140116       Rejected
10          20140121       Rejected

And would like to get the results like so:

[Month]    Accepted    Total
-------    --------    -----
2013-05    1           2
2013-06    2           3
2013-08    2           2
2013-10    1           1
2014-01    2           2

How do I go about it?

Upvotes: 0

Views: 65

Answers (3)

Marco
Marco

Reputation: 77

Try it:

SELECT 
    CONVERT(VARCHAR(4), YEAR(Date)) + '-' + CONVERT(VARCHAR(2), MONTH(Date)) Period, 
    SUM(CASE WHEN Status = 'Accepted' THEN 1 ELSE 0) Accepted,
    COUNT(*) Total

FROM Orders
GROUP BY YEAR(Date), MONTH(Date)
ORDER BY YEAR(Date), MONTH(Date)

Upvotes: 0

user275683
user275683

Reputation:

To get yyyy-dd format you can use this

SELECT CONVERT(VARCHAR(7),[Date],20)
   ,COUNT(CASE WHEN [status] = 'Accepted' THEN 1
               ELSE NULL END) AS 'Accepted'
   ,COUNT(*) AS Total
FROM Orders
GROUP BY CONVERT(VARCHAR(7),[Date],20)

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

Assuming you will never have a time component, this should work just fine:

DECLARE @d TABLE([Order] INT, [Date] DATETIME, [Status] CHAR(8));

INSERT @d VALUES
(1 ,'20130501','Accepted'),
(2 ,'20130509','Rejected'),
(3 ,'20130610','Accepted'),
(4 ,'20130614','Accepted'),
(5 ,'20130626','Rejected'),
(6 ,'20130802','Accepted'),
(7 ,'20130801','Accepted'),
(8 ,'20131014','Accepted'),
(9 ,'20140116','Rejected'),
(10,'20140121','Rejected');

SELECT 
  [Month] = DATEADD(DAY, 1-DAY([Date]), [Date]),
  Accepted = SUM(CASE WHEN [Status] = 'Accepted' THEN 1 ELSE 0 END),
  COUNT(*)
FROM @d
GROUP BY DATEADD(DAY, 1-DAY([Date]), [Date])
ORDER BY [Month];

(And if you are on SQL Server 2008 or newer, you should use the DATE data type to prevent having to deal with any errant hours/minutes.)

If you can have hours/minutes sometimes, and you're not on 2008 or greater, then:

SELECT 
  [Month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0),
  Accepted = SUM(CASE WHEN [Status] = 'Accepted' THEN 1 ELSE 0 END),
  COUNT(*)
FROM @d
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)
ORDER BY [Month];

I strongly recommend avoiding any solutions that group by using string conversions. Date/time math is much more efficient in SQL Server than converting to strings. Also if you want the client side to present things like 2013-05, use Format(), ToString() etc. to apply that string formatting on the client.

Upvotes: 2

Related Questions