[devtalk] Retrieving monthly counts for multiple choice answers from MySQL for charting

[Ross Clutterbuck]

Hi list

Been a while - hope you've all been staying out of trouble.

My knowledge of SQL is severely lacking and I'm finding what I feel 
should be simple tasks quite baffling, and I'm not getting anywhere 
searching online. So, you beautiful SQL people, I turn to you.

I'm incorporating some line chart trend analysis for a feedback system 
I've written. The feedback itself is 4 multiple-choice questions and 
is easily done, but I want now to retrieve some totals for those 
multiple choice answers over a period of months.

The database comprises of a field for each question which stores a 
numerical value 1 to 5 and a date posted field in the format 
yyyy-mm-dd (standard MySQL Date field I believe). What I want to do is 
retrieve the counts for each answer to each question for x number of 
months, for example:

January:
Q1: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5
Q2: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5
Q3: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5
Q4: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5

February:
Q1: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5
Q2: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5
Q3: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5
Q4: ? people rated 1, ? people rated 2, ? people rated 3, ? people 
rated 4, ? people rated 5

etc.

It doesn't matter if I have to use a separate query for each question 
over the month range, or a separate query for each month for all 
questions.


Any pointers? Clear as mud? I'm using MySQL 5 and PHP 5.

Hope to hear from you soon.

Ross