Sumon Sarker Sumon Sarker - 3 months ago 8
MySQL Question

How can I write following MySQL query more efficient?

Here is users table and id is the primary key. [One to Many relationship with attendances table]

+------------+-----------------+
| id | name | created |
+------------+-----------------+
| 1 | Sumon | 2015-01-10 |
+------------+-----------------+
| 2 | James | 2015-01-11 |
+------------+-----------------+
| ...| ... | ... |
+------------+-----------------+


Here is another table attendances. Here id is the primary key and user_id is the foreign key of users table

+--------------+------+--------+-----------------+
| id | user_id | year | month | created |
+--------------+------+--------+-----------------+
| 1 | 2 | 2015 | 1 | 2015-01-10 |
+--------------+---------------+-----------------+
| 2 | 1 | 2016 | 2 | 2016-02-10 |
+--------------+------+--------+-----------------+
| 3 | 1 | 2016 | 2 | 2016-02-11 |
+--------------+------+--------+-----------------+
| ...| ... | ... | ... | ... |
+--------------+------+--------+-----------------+


My current MySQL query

SELECT COUNT(DISTINCT user_id) AS january,
(SELECT COUNT(DISTINCT user_id) FROM attendances WHERE year='2016' AND month=2) as february,
(SELECT COUNT(DISTINCT user_id) FROM attendances WHERE year='2016' AND month=3) as march,
...
...
(SELECT COUNT(DISTINCT user_id) FROM attendances WHERE year='2016' AND month=12) as december
FROM attendances WHERE year='2016' AND month=1


Result of my above MySQL query like as below

+---------+----------+-------+-------+-----+-------+------+--------+-----------+---------+----------+----------+
| january | february | march | april | may | june | july | august | september | october | november | december |
+---------+----------+-------+-------+-----+-------+------+--------+-----------+---------+----------+----------+
| 24 | 17 | 20 | 0 | 24 | 23 | 19 | 24 | 13 | 0 | 0 | 0 |
+---------+----------+-------+-------+-----+-------+------+--------+-----------+---------+----------+----------+


Is there any way to write this same query more efficiently?

Answer

You could use CASE WHEN:

SELECT COUNT(DISTINCT (CASE WHEN month = 1 THEN user_id END)) as january,
       COUNT(DISTINCT (CASE WHEN month = 2 THEN user_id END)) as february,
       COUNT(DISTINCT (CASE WHEN month = 3 THEN user_id END)) as march

FROM   attendances
WHERE  year='2016'
Comments