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';


   count(pkID) as TRecords 
FROM students 
    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')

   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:

   count(s.pkID) as TRecords,
   (select count(pkID)
    from #students
            YEAR(DateAdded) <= YEAR(s.DateAdded) 
            and MONTH(DateAdded) <= MONTH(s.DateAdded)
FROM #students s
group by 
   MONTH(s.DateAdded), YEAR(s.DateAdded)
  • I need to show all 12 months data for the selected year. – Deepak Dholiyan Sep 22 '17 at 14:49
  • Like Jan 17000 Feb 18000 Mar 19500 – Deepak Dholiyan Sep 22 '17 at 14:49
  • Your answer is not correct, please check my posted query and post correct answer if you know – Deepak Dholiyan Sep 22 '17 at 14:50
  • So is it a 'month to date' value what you want? so probably a window-fuction is the best. – Christian4145 Sep 22 '17 at 15:08
  • 1
    You're using Window (Analytic) functions! Still beta in MySQL! – Vérace Sep 22 '17 at 15:27

Finally after searching a lot on this I resolved this.

SELECT COUNT(pkID) as TRecords 
FROM students 
WHERE (fkCategoryID = 56 OR fkSecondaryCategoryID = 56 OR fkOptionalCategoryID = 56)
AND DATE_FORMAT(DateAdded, '%Y-%m') <= '2015-01';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.