Iván Iván - 3 months ago 9
MySQL Question

Group by year and month but only present the month and year actual mysql

I have the following table.

select * from `consumo`;


Outputs:

+----+------------+-----------------+---------------------+
| id | cedula | registroConsumo | fecha-hora |
+----+------------+-----------------+---------------------+
| 1 | 0931111111 | 20 | 2016-08-17 13:52:48 |
| 2 | 0931111111 | 25 | 2016-08-17 13:53:01 |
| 3 | 0311111111 | 27 | 2016-08-17 13:54:23 |
| 4 | 0311111111 | 10 | 2016-08-17 13:54:26 |
| 5 | 0311111111 | 12 | 2016-08-17 13:54:29 |
| 6 | 0311111111 | 14 | 2016-08-17 13:54:34 |
| 7 | 0311111111 | 19 | 2016-08-17 13:54:38 |
| 8 | 0311111111 | 190 | 2016-08-17 13:54:44 |
| 9 | 0311111111 | 195 | 2016-08-17 13:54:48 |
| 10 | 0912223333 | 19 | 2016-08-17 13:54:54 |
| 11 | 0111111111 | 20 | 2016-08-17 13:54:59 |
| 12 | 0111111111 | 23 | 2016-08-17 13:55:03 |
| 13 | 0111111111 | 27 | 2016-08-17 13:55:07 |
| 14 | 0111111111 | 35 | 2016-08-17 13:55:11 |
| 15 | 0111111111 | 10 | 2016-08-17 13:55:14 |
| 16 | 0111111111 | 35 | 2016-09-04 12:10:01 |
| 17 | 0111111111 | 330 | 2016-09-04 12:10:06 |
+----+------------+-----------------+---------------------+


I would like to group by year and month but only present the result of month and year actual all is in Mysql.

I did this:

select SUM(`registroConsumo`) from `consumo`
GROUP BY YEAR(`fecha-hora`),MONTH(`fecha-hora`)
HAVING YEAR(`fecha-hora`) = SELECT EXTRACT (YEAR FROM (SELECT NOW()))
AND MONTH(`fecha-hora`) = SELECT EXTRACT (MONTH FROM (SELECT NOW())) ;


I get this error:


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
SELECT EXTRACT (YEAR FROM (SELECT NOW())) AND MONTH(`fecha-hora`) = SELECT EXTRA
at line 1

Answer

You have several missing parentheses in the sub-queries that appear in the having clause.

But you could approach this more pragmatically, and just return the most recent data you get, with the use of order by and limit:

select   YEAR(`fecha-hora`),
         MONTH(`fecha-hora`)
         SUM(`registroConsumo`) 
from     `consumo` 
group by YEAR(`fecha-hora`),
         MONTH(`fecha-hora`)
order by YEAR(`fecha-hora`) DESC,
         MONTH(`fecha-hora`) DESC
limit 1

Or, if it really has to match with the current year and month:

select   YEAR(`fecha-hora`),
         MONTH(`fecha-hora`)
         SUM(`registroConsumo`) 
from     `consumo` 
where    YEAR(`fecha-hora`) = YEAR(NOW())
and      MONTH(`fecha-hora`) = MONTH(NOW())
group by YEAR(`fecha-hora`),
         MONTH(`fecha-hora`)