I have a table. I need to fetch result and show in line graph. I need result of count(id) and type monthly wise. I have tried a query but not working. In graph I need to show two type(booking and membership). Is it possible to get two types and count monthly wise in a single query?
SELECT COUNT(id), type, MONTHNAME(created_at) FROM orders WHERE YEAR(created_at) = 2016 ORDER BY created_at GROUP BY MONTH(created_at)
id type created_at
1 booking 2016-01-12
2 membership 2016-01-12
3 booking 2016-05-11
4 booking 2016-05-15
5 booking 2016-07-07
6 membership 2016-07-08
7 membership 2016-07-09
8 membership 2016-08-16
9 booking 2016-08-17
10 membership 2016-08-19
I think you just want conditional aggregation:
SELECT MONTHNAME(created_at), SUM(type = 'Booking') as Booking, SUM(type = 'Membership') as Membership FROM orders WHERE YEAR(created_at) = 2016 GROUP BY MONTH(created_at) ORDER BY MIN(created_at );
GROUP BYcolumns first in an aggregation query and the calculated columns after them. (Personal preference).
SUM()is counting the number of true values. In most other databases, you need to use a
ORDER BYgoes after the
CREATED_AT, because columns not in the
GROUP BYshould never be "bare".