MySQl Query optimization

Question

I have a table with a list of tasks. Each task has a datetime field called "completedTime". Basically everytime a task is marked completed that field gets updated with the correct time.

Now I need to do a graph (using jQuery) for this result where the x axis is the months of the year (jan-dec) and the y axis is a number.

What is the sql query can I use so it would spit out 12 columns (Jan-Dec) with a number in each depending on how many tasks have a completedTime in that month.

I don't want to run the query below 12 times or each month.

SELECT * FROM `tasks` WHERE month(completedTime) between '02' and '03';

Any ideas?

What is the structure of the tasks table? Anyway, it's either that query 12 times or one query with 12 subqueries. I think the first option is much more maintainable — pixeline
– pixeline, Commented Jan 2, 2012 at 1:07

Gerald P. Wright · Accepted Answer · 2012-01-02 01:47:05Z

1

If I understand correctly, your want it to return 12 rows (one for each month) with a count of the number of tasks.

If that is correct, then something like this should work. I added the year, which could be parametrized.

SELECT Count(*)
FROM Tasks
WHERE Year = 2011
GROUP BY Month(completedTime);

Revised with name for Month

SELECT Count(*) as total, 
    DateName(month, DateAdd(month, Month(completedTime), 0 ) - 1 ) as Month 
FROM tasks 
WHERE year(completedTime) = '2011' 
GROUP BY Month(completedTime)

edited Jan 2, 2012 at 1:47

answered Jan 2, 2012 at 1:09

Gerald P. Wright

7964 silver badges9 bronze badges

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

5 Comments

user1125562 Over a year ago

thanks for hint! i knew it had something to do with group by :P here is what i did with it: SELECT Count(*) as total, Month(completedTime) as Month FROM tasks WHERE year(completedTime) = '2011' GROUP BY Month(completedTime) the only other question is, the MONTH function returns a numerical value for the month (3 for march for example). is there anyway to get it to return the month name? if not, i can do that in PHP. thanks again

user1125562 Over a year ago

also it would be nice to display the months that have nothing as a 0.

Gerald P. Wright Over a year ago

To get a month name instead of number change Month(completedTime) to DateName( month , DateAdd( month , Month(completedTime), 0 ) - 1 )

Gerald P. Wright Over a year ago

Sadly, I'm not sure how to account for zero count months right off the top of my head. I'll think about it a bit more.

user1125562 Over a year ago

thank you so much for all the help. i was able to use the above SQL to spit out the info i needed and just used a PHP array with the month names to associate each number to the name. And instead of looping through the sql resulstset i loop through the array which takes care of the 0 for the months with no info.

Collectives™ on Stack Overflow

MySQl Query optimization

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related