Selecting distinct months and years and then breakdown of values for

Question

I'm using Open Flash Chart to create statistics and one of the things i need to be able to do is generate a Stacked Bar Chart.

Logically i need to be able to Group all the distinct Month/Year combinations, Dec 2009, Jan 2010, Feb 2010 etc. and then from that group all the various rows, i.e. the different types of enquiry a visitor made (via website, via email, via phonecall)

at the moment the table attributes look like this:

id (int, auto increment) date_time(date time format) type (enum, visit, website, phone, email)

Any suggestions? I've tried a few things and haven't had much luck.

Answer 1

Something like this should work:

select count(*), type, YEAR(date_time), MONTH(date_time) from `table` 
group by type, YEAR(date_time), MONTH(date_time)

Answer 2

SELECT  EXTRACT(YEAR_MONTH FROM date_time) AS ym, type, COUNT(*)
FROM    mytable
GROUP BY
        ym, type

Answer 3

You could create a table with a month field, and populate it for the months you're interested in, for example:

StartOfMonth
2010-01-01
2010-02-01
2010-03-01
...

Then you can use left join to group on all months:

select     
    year(mt.StartOfMonth)
,   month(mt.StartOfMonth)
,   e.type
,   count(*)
from       MonthTable mt
left join  Enquiries e
on         mt.StartOfMonth <= e.EnquiryDate
           and e.EnquiryDate < mt.StartOfMonth + interval 1 month
where      mt.StartOfMonth <= NOW()
group by   year(mt.StartOfMonth), month(mt.StartOfMonth), e.type

Answer 4

To group by year and month the sql can look like this:

SELECT DATE_FORMAT(date_time, '%Y-%m') AS yearmonth, 
       COUNT(*) AS count_month
FROM table_name 
GROUP BY yearmonth