One for the Mysql Masters!

[scotchegg78]

I have a working query which returns a count of all rows in a table per month....

 

select date_format(registrantDate, '%M'), count(*) from cr_registrant where registrantDate >= date_sub(now(),interval 12 month) group by date_format(registrantDate, '%M')

 

however within this total per month I would like it also broken up of into the various applicants per region.

The cr_registrant table has a filed which stores the officeID selected of the applicant. Of which the officeID in the cr_office table links the office to its region using cr_office.regionIDfk. of course cr_region has the regionID key and regionName key.

 

What I would like is to output the date so its columns are something like..

 

Month, Total Qty,(and then region totals that month..) South Region, North Region .. etc etc

 

Any ideas how or if this can be done in one qry, or shall i just do it in two?

[fenway]

You can all multiple columns to the group by.