Conso par mois

gistfile1.sql

MariaDB [conso]> select DATE_FORMAT(DATE, '%Y-%m'), sum(cIndexDelta) from conso_releve where HOUR(DATE) < 8 AND HOUR(DATE) < 18 GROUP BY DATE_FORMAT(DATE, '%Y-%m') ORDER BY DATE_FORMAT(DATE, '%Y-%m');
+----------------------------+------------------+
| DATE_FORMAT(DATE, '%Y-%m') | sum(cIndexDelta) |
+----------------------------+------------------+
| 2016-12                    |             3480 |
| 2017-01                    |           229778 |
| 2017-03                    |            79683 |
| 2017-05                    |            79782 |
+----------------------------+------------------+
4 rows in set (0.19 sec)


MariaDB [conso]> select DATE_FORMAT(DATE, '%Y-%m-%d'), sum(cIndexDelta) from conso_releve where HOUR(DATE) < 8 AND HOUR(DATE) < 18 GROUP BY DATE_FORMAT(DATE, '%Y-%m-%d');
+-------------------------------+------------------+
| DATE_FORMAT(DATE, '%Y-%m-%d') | sum(cIndexDelta) |
+-------------------------------+------------------+
| 2016-12-28                    |             2449 |
| 2016-12-29                    |             1031 |
| 2017-01-17                    |            17189 |
| 2017-01-18                    |            17781 |
| 2017-01-19                    |            19610 |
| 2017-01-20                    |            18636 |
| 2017-01-21                    |            21047 |
| 2017-01-22                    |            15479 |
| 2017-01-23                    |            13949 |
| 2017-01-24                    |            17082 |
| 2017-01-25                    |            15138 |
| 2017-01-26                    |            20666 |
| 2017-01-27                    |            15784 |
| 2017-01-28                    |            12574 |
| 2017-01-29                    |             8293 |
| 2017-01-30                    |             8283 |
| 2017-01-31                    |             8267 |
| 2017-03-01                    |             8074 |
| 2017-03-02                    |             5526 |
| 2017-03-03                    |             8322 |
| 2017-03-04                    |             7515 |
| 2017-03-05                    |             6432 |
| 2017-03-06                    |             7282 |
| 2017-03-21                    |             5106 |
| 2017-03-22                    |             7109 |
| 2017-03-23                    |             5544 |
| 2017-03-24                    |             7243 |
| 2017-03-25                    |             5829 |
| 2017-03-26                    |             3558 |
| 2017-03-27                    |             2143 |
| 2017-05-13                    |             7668 |
| 2017-05-14                    |             2826 |
| 2017-05-15                    |             2918 |
| 2017-05-16                    |             3074 |
| 2017-05-17                    |             2911 |
| 2017-05-18                    |             3081 |
| 2017-05-19                    |             4463 |
| 2017-05-20                    |             8662 |
| 2017-05-21                    |             2897 |
| 2017-05-22                    |             4613 |
| 2017-05-23                    |             3651 |
| 2017-05-24                    |             3705 |
| 2017-05-25                    |             5245 |
| 2017-05-26                    |             2831 |
| 2017-05-27                    |             7862 |
| 2017-05-28                    |             4809 |
| 2017-05-29                    |             4251 |
| 2017-05-30                    |             4315 |
+-------------------------------+------------------+
48 rows in set (0.21 sec)


MariaDB [conso]> SELECT month, avg(conso) FROM (select DATE_FORMAT(DATE, '%Y-%m') as month , sum(cIndexDelta) as conso from conso_releve where HOUR(DATE) < 8 AND HOUR(DATE) < 18 GROUP BY DATE_FORMAT(DATE, '%Y-%m-%d') ) AS daily GROUP BY month;
+---------+------------+
| month   | avg(conso) |
+---------+------------+
| 2016-12 |  1740.0000 |
| 2017-01 | 15318.5333 |
| 2017-03 |  6129.4615 |
| 2017-05 |  4432.3333 |
+---------+------------+
4 rows in set (0.19 sec)