Selecting distinct months and years and then breakdown of values for
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 incr开发者_JAVA百科ement) 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.
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)
SELECT EXTRACT(YEAR_MONTH FROM date_time) AS ym, type, COUNT(*)
FROM mytable
GROUP BY
ym, type
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
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
精彩评论