How to show column item counts by month and current year only

[phpnewby]

The simple database has a few columns ID, Created, Name and Primary_Interest. The Primary Interest column has items that include Family Photo Session, Maternity Photo Session, Prom Photo Session and Graduation Photo Session. I am trying to figure out how to display column item group counts from Primary_Interest by month and the current calendar year only.

The code below works but shows the total count by group in the column. The date format in the Created column is YYYY-MM-DD HH-MI-SS. I just want the month and a count by group for Primary_Interest for the current year only. How would I do this?

SELECT Primary_Interest, Count(*) FROM xjxig_Request_Session GROUP BY Primary_Interest;

Any help is appreciated.

[phdr]

You can GROUP BY multiple things. This will let you get a count for each Primary_Interest, then each month part of the Created date (you would want to SELECT this as well.) To limit the result to the current year, you would add a WHERE clause to compare the year part of the Created date with the current year. The following should (untested) work -

SELECT Primary_Interest, Count(*) as total
 FROM xjxig_Request_Session
 WHERE YEAR(Created) = YEAR(NOW())
 GROUP BY Primary_Interest, MONTH(Created)

Note: if you add an alias name to the count(*) expression, e.g. the as total in the above (the as keyword is optional and can be left out), you can reference it by the alias in your php code.

[phpnewby]

Is it possible to show the total data for each month for the current year?

For example you have the 12 months:
January shows Family Photo Session 1, High School Prom Photo Session 2
February Family Photo Session 1, High School Prom Photo Sessions 3, Maternity Photo Session 1
March Family Photo Session 2

[phdr]

Yep. You can get totals for each change in GROUP BY things, including a grand-total at the end. Add the following after the GROUP BY a,b term -

WITH ROLLUP