Select mysql with COUNT and GROUP BY MONTH

Asked

Viewed 331 times

0

Hello,

I am trying to put together the information of two tables to generate a graph, I need to count the total of record consults1 and consults2 and group by month:

Currently I have this return:

consults1:

MariaDB [logview]> SELECT MONTH(data) as MES, COUNT(id_consulta) as TOTAL1 FROM consultas1 group by MONTH(data);
+------+--------+
| MES  | TOTAL1 |
+------+--------+
|    1 |    158 |
|    2 |    125 |
|    3 |     84 |
|    4 |    227 |
|    5 |    117 |
|    6 |    117 |
|    7 |    167 |
|    8 |    170 |
|    9 |     86 |
|   10 |    165 |
|   11 |    164 |
|   12 |    186 |
+------+--------+

consults2

MariaDB [logview]> SELECT MONTH(data) as MES, COUNT(id_consulta) as TOTAL1 FROM consultas2 group by MONTH(data);
+------+--------+
| MES  | TOTAL1 |
+------+--------+
|    8 |      9 |
+------+--------+

Expected result:

+------+--------+--------+
| MES  | TOTAL1 | TOTAL2 |
+------+--------+--------+
|    1 |    158 |    0   |
|    2 |    125 |    0   |
|    3 |     84 |    0   |
|    4 |    227 |    0   |
|    5 |    117 |    0   |
|    6 |    117 |    0   |
|    7 |    167 |    0   |
|    8 |    170 |    9   |
|    9 |     86 |    0   |
|   10 |    165 |    0   |
|   11 |    164 |    0   |
|   12 |    186 |    0   |
+------+--------+--------+

Could someone help me?

mysql select date group-by count

1 answer

0

Try with a merge between tables:

SELECT  MONTH(COALESCE(consultas1.data, consultas2.data)) as MES, 
            COALESCE(COUNT(consultas1.id_consulta), 0) as TOTAL1, 
            COALESCE(COUNT(consultas2.id_consulta), 0) as TOTAL2 
FROM consultas1 FULL OUTER JOIN consultas2 ON (MONTH(consultas1.data) = MONTH(consultas2.data))
GROUP BY 1;

Depending on the particularity of your table queris1 (if it defines which months to consider) you can use LEFT OUTER JOIN.

  • You have an error in your SQL syntax; check the manual that Corresponds to your Mariadb server version for the right syntax to use near 'OUTER JOIN

  • See: https://dev.mysql.com/doc/refman/8.0/en/join.html

  • It seems that Mariadb does not use FULL in the junction: https://mariadb.com/kb/en/library/join-syntax/

  • You can implement the operation of FULL OUTER JOIN by making a UNION of a LEFT OUTER JOIN with a RIGHT OUTER JOIN but, as already commented, you have to verify if this is effectively necessary according to your data. For the example presented, a LEFT OUTER JOIN would be enough (the table consults1 contains all the months to be considered).

Browser other questions tagged mysql select date group-by count

You are not signed in. Login or sign up in order to post.