MySQL Specific Query Requirement

I have datetime field names "salestime" in "sales" table

I need to get data as:

SalesMonthYear      Total
2010-11             10
2010-10             15
2010-09             21

or

Nov-2010            10
Oct-2010            15
Sep-2010            21

Any one know how can I achieve this using qu开发者_高级运维ery? Thanks for the help

You need to use DATE_FORMAT and GROUP BY to achieve what you want. This query will do it:

SELECT DATE_FORMAT(salestime, '%Y-%m') as SalesMonthYear, count(*) as `Total` FROM `sales` GROUP BY SalesMonthYear ORDER BY `salestime`

And for the second version:

SELECT DATE_FORMAT(salestime, '%b-%Y') as SalesMonthYear, count(*) as `Total` FROM `sales` GROUP BY SalesMonthYear ORDER BY `salestime`

SELECT DATE_FORMAT(salesTime, '%Y-%m') AS salesMonthYear,
       COUNT(*) AS salesCount
FROM sales
GROUP BY DATE_FORMAT(salesTime, '%Y-%m')