Thesql need to show all months data for the selected year

MySQLPHPphpmyadmin

I got stuck with my query. I need to show monthl (current + previous) records.

Let's take below example for year 2014:-

I have 7 records in the month 03(March)

I have 2 records in the month 04(April)

So total records in year 2014 is 9.

Now we come to year 2015:-

I need to show all records in Jan(No new records added in database), This should be 9.

My Query shows 0.

I need to show all records in Feb(5 new records added in database), This should be 14.

My query shows 5.

Query works fine when I am checking for month 03 and year 2014, showing all 7 records. but not for all conditions.

I am calling same query for all months(01-12).

SELECT count(pkID) as TRecords 
FROM   students 
WHERE  (fkCategoryID ='56' 
OR     fkSecondaryCategoryID ='56' 
OR     fkOptionalCategoryID ='56') 
AND    MONTH(`DateAdded`) <='1' 
AND    YEAR(`DateAdded`) <='2015';

Best Answer

Maybe:

SELECT 
   MONTH(`DateAdded`), 
   YEAR(`DateAdded`),
   count(pkID) as TRecords 
FROM students 
WHERE 
    fkCategoryID ='56' OR 
    fkSecondaryCategoryID ='56' OR 
    fkOptionalCategoryID ='56'
group by 
    MONTH(`DateAdded`), YEAR(`DateAdded`)

Or maybe some more filters. Your question is not all clear.

Or maybe:

if object_id ('tempdb..#students') is not null drop table #students


create table #students 
    (
    pkID int,
    DateAdded date
    )

 insert #students values 
      (1, '20100101'),
      (1, '20100201'),
      (1, '20100301'),
      (1, '20100401'),
      (1, '20100501'),
      (1, '20100601'),
      (2, '20100101'),
      (2, '20100201'),
      (2, '20100301')

SELECT 
   MONTH(DateAdded), 
   YEAR(DateAdded),
   count(pkID) as TRecords,
   sum(count(pkID)) over(partition by YEAR(DateAdded) order by MONTH(DateAdded))
FROM #students 
group by 
    MONTH(DateAdded), YEAR(DateAdded)
 order by 2,1

Or maybe:

SELECT 
   MONTH(s.DateAdded), 
   YEAR(s.DateAdded),
   count(s.pkID) as TRecords,
   (select count(pkID)
    from #students
        where 
            YEAR(DateAdded) <= YEAR(s.DateAdded) 
            and MONTH(DateAdded) <= MONTH(s.DateAdded)
   )
FROM #students s
group by 
   MONTH(s.DateAdded), YEAR(s.DateAdded)
Related Question