SQL Query Group By Mount And Year

Question

I have this table, called history (dates are in DD-MM-YYYY):

====================
| Buy        | Qty |
====================
| 01-01-2012 |  1  |
| 01-01-2012 |  1  |
| 01-02-2012 |  1  |
| 01-03-2012 |  1  |
| 01-05-2012 |  1  |
| 01-07-2012 |  1  |
| 01-12-2012 |  1  |
====================

NOTE: There was no purchase in months 4, 6, 8, 9, 10, 11.

If I run :

SELECT MONTH(buy) AS day, YEAR(buy) as year, SUM(qty)
FROM history
GROUP BY MONTH(buy),YEAR(buy)

I get this result:

======================
| Month | Year | Qty |
======================
| 01    | 2012 |  2  |
| 02    | 2012 |  1  |
| 03    | 2012 |  1  |
| 05    | 2012 |  1  |
| 07    | 2012 |  1  |
| 12    | 2012 |  1  |
======================

I want months 4, 6, 8, 9, 10, 11 to show too but have Qty of 0 (zero), like this:

======================
| Month | Year | Qty |
======================
| 01    | 2012 |  2  |
| 02    | 2012 |  1  |
| 03    | 2012 |  1  |
| 04    | 2012 |  0  |
| 05    | 2012 |  1  |
| 06    | 2012 |  0  |
| 07    | 2012 |  1  |
| 08    | 2012 |  0  |
| 09    | 2012 |  0  |
| 10    | 2012 |  0  |
| 11    | 2012 |  0  |
| 12    | 2012 |  1  |
======================

How can I do this?

score 1 · Answer 1 · edited Aug 05 '12 at 07:02

You could use a look-up table of integers.

What I use in this situation is a function/procedure that generates the number via a CTE (Example below)

The rest would be to create dates using the resulting number as the month - and then join your results from above.

declare @min int 
declare @max int

set @min = 1
set @max = 12
;
with numbers(n) as
(
    select @min as n 
    union all 
    select n + 1
    from numbers
    where n + 1 <= @max 
)
select row_number() over (order by n) [row], n
from numbers option(maxrecursion 0);

praveen · Accepted Answer · 2012-08-05T07:34:17.477

Try this :

Declare @Sample table 
(Buy datetime ,Qty int)

Insert into @Sample values
( '01-01-2012' ,1),
('01-01-2012',1 ),
('01-02-2012',1 ),
('01-03-2012',1 ),
('01-05-2012',1 ),
('01-07-2012',1 ),
('01-12-2012',1 )

;with cte as 
(
  select top 12 row_number() over(order by t1.number) as N
  from   master..spt_values t1 
   cross join master..spt_values t2
 )
select t.N as month,
isnull(datepart(year,y.buy),'2012') as Year,
sum(isnull(y.qty,0)) as Quantity
from cte t
left join @Sample y
on month(convert(varchar(20),buy,103)) = t.N
group by y.buy,t.N

Create a Month table to store the value from 1 to 12 .Instead of master..spt_values you can also use sys.all_objects

  select row_number() over (order by object_id) as months
  from sys.all_objects

or use a recursive cte to generate the month table

;with cte(N) as 
(
Select 1 
union all
Select 1+N from cte where N<12
)
Select * from cte

and then use Left join to compare the value from the month table with your table and use isnull function to handle the null values.

It contains numbers from 0 to 2047 which is very useful in creating numbers tables as i have shown above .Kindly refer this article http://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-me. — praveen, Aug 06 '12 at 02:28

score 1 · Answer 3 · answered Aug 06 '12 at 05:20

Below code will helps you, you can also filter data by year

SELECT MonthYearTable.Month , MonthYearTable.Year, ISNULL(SUM(qty),0)
FROM history
Right JOIN (SELECT Month, Year FROM (SELECT 1 AS Month 
                            Union SELECT 2 
                            Union SELECT 3 
                            Union SELECT 4 
                            Union SELECT 5 
                            Union SELECT 6 
                            Union SELECT 7 
                            Union SELECT 8 
                            Union SELECT 9 
                            Union SELECT 10 
                            Union SELECT 11 
                            Union SELECT 12) AS MonthTable 
            INNER JOIN (SELECT DISTINCT YEAR(buy) AS year FROM history) YearTable ON 1=1
            ) AS MonthYearTable ON Month(history.buy) = MonthYearTable.Month AND Year(history.buy) = MonthYearTable.Year
GROUP BY MonthYearTable.Month, MonthYearTable.Year
ORDER BY MonthYearTable.Year, MonthYearTable.Month

score 0 · Answer 4 · answered Aug 05 '12 at 06:05

Try This:

CREATE TABLE history(Buy datetime,Qty int) 

INSERT INTO history
VALUES('01-01-2012',1),
 ('01-01-2012',1),  
 ('01-02-2012',1),  
 ('01-03-2012',1),  
 ('01-05-2012',1),  
 ('01-07-2012',1),  
 ('01-12-2012',1)  


Declare @mindate datetime
Declare @maxdate datetime
select @mindate=MIN(convert(datetime,buy,103)),@maxdate=MAX(convert(datetime,buy,103)) from history 


select datepart(month,a.dt) as month,datepart(year,a.dt) as year,isnull(SUM(qty),0) as Quantity  
from 
(select DATEADD(mm,number,convert(varchar(10),@mindate,103)) as dt from master..spt_values 
where type='p' and DATEADD(mm,number,convert(varchar(10),@mindate,103)) <= convert(varchar(10),@maxdate,103)
) a left join history h
on DATEPART(month,a.dt) = DATEPART(month,convert(varchar(10),h.Buy ,103))
group by a.dt 
order by a.dt

SQL Query Group By Mount And Year

4 Answers4