need help in sql query with counting values within dates

Question

the table:

tab_tasks

id|....|date_completed|completed
1 |    |  2016-11-05  |   Y
2 |    |  2016-11-07  |   N
3 |    |  2016-11-09  |   Y
4 |    |  2016-12-11  |   Y
5 |    |  2017-01-15  |   Y
6 |    |  2017-01-30  |   Y

and so on...

I need help in writing sql query by giving current date and get months with counted completed tasks back to max 12 months something like:

month        | num_complited
november     |     2
december     |     1
january      |     2

or if it would be easier the instead of letters of the alphabet, month can be numeric 11, 12, 01 in column month.

How can be this accomplished? Is there a need for any php code or can be this done with just sql query? Or should I add extra column to that table, like month?

krishn Patel · Accepted Answer · 2017-03-22 09:53:19Z

1

you should put some date range otherwise for example 11-2016 has 6 count and 11-2017 has 5 so out put will show 11 count data mix-up

 SELECT MONTHNAME(date_completed) as month ,
  count(completed)  as num_complited 
  from tab_tasks 
  where completed='Y' and 
  date_completed between '2016-11-05'  and '2017-11-05' 
  group by MONTHNAME(date_completed)

edited Mar 22, 2017 at 9:53

answered Mar 22, 2017 at 9:18

krishn Patel

2,5991 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

felixRo Over a year ago

this answer seems to be what I'd need, but I don't know why I get empty result if I add your date range? (of course my records are in that date range) When I remove the date condition then shows fine, any idea?

krishn Patel Over a year ago

may be you try like '2016-11-05'

krishn Patel Over a year ago

can you try now I have added single quotas

krishn Patel · Accepted Answer · 2017-03-22 09:32:14Z

0

Try this:

select MONTHNAME(date_completed) as MosName,count(1) as num_completed  
from TableName
where completed = 'Y'
GROUP BY MONTHNAME(date_completed);

edited Mar 22, 2017 at 9:32

krishn Patel

2,5991 gold badge22 silver badges31 bronze badges

answered Mar 22, 2017 at 9:16

Vijunav Vastivch

4,2111 gold badge18 silver badges32 bronze badges

1 Comment

krishn Patel Over a year ago

you forget to add from table

Collectives™ on Stack Overflow

need help in sql query with counting values within dates

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related