[Solved] MYSQL SELECT AND COUNT MULTIPLE ROWS BY MONTH

Question

In order to compute aggregate values you have to use GROUP BY

SELECT EXTRACT(YEAR_MONTH FROM `date`) AS year_month, COUNT(*) AS cnt
FROM tableName
WHERE `status` = 'sold'
GROUP BY EXTRACT(YEAR_MONTH FROM `date`)

Keep in mind that, because of the function EXTRACT() used in the GROUP BY clause, MySQL cannot use an index to optimize the processing of the GROUP BY and, if the WHERE condition selects a large number of rows the query will be slow.

Accepted Answer

In order to compute aggregate values you have to use GROUP BY

SELECT EXTRACT(YEAR_MONTH FROM `date`) AS year_month, COUNT(*) AS cnt
FROM tableName
WHERE `status` = 'sold'
GROUP BY EXTRACT(YEAR_MONTH FROM `date`)

Keep in mind that, because of the function EXTRACT() used in the GROUP BY clause, MySQL cannot use an index to optimize the processing of the GROUP BY and, if the WHERE condition selects a large number of rows the query will be slow.