sam sam sam sam - 3 months ago 10
MySQL Question

How can select two types and count id monthly wise using mysql?

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?

SQL Query

SELECT COUNT(id), type, MONTHNAME(created_at) FROM orders WHERE YEAR(created_at) = 2016 ORDER BY created_at GROUP BY MONTH(created_at)


orders table

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


Expecting output is :
eg: In this case (2016-07-07) I need count is 3 and type is "booking, membership, membership"

Answer

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 );

Notes:

  • I usually put the GROUP BY columns first in an aggregation query and the calculated columns after them. (Personal preference).
  • MySQL treats a boolean expression as "1" for true and "0" for false, so the SUM() is counting the number of true values. In most other databases, you need to use a CASE expression.
  • The ORDER BY goes after the GROUP BY.
  • When creating aggregation queries, don't learn bad habits. Hence, I put an aggregation function around CREATED_AT, because columns not in the GROUP BY should never be "bare".