CODEWITHSUNDEEP
mysqlsqldate
Puetong0815
Puetong0815

Reputation: 35

MySql Query for a yearly / montly report

Hello I am having a Table, that is filled by my home automation. I princinle there are all the values in just one table. Some entries are made by a statistical modul. It calculates the monthly water consumption. Now I want to create a query, that give a mounthly view of the comsumption for each year.

Like:

|Month|2018|2019|  
|Jan..|7.3 |6.8|  
|Feb  |5.9 |7.8|....

The query for one year is:

SELECT
    monthname(TIMESTAMP) Monat,
    VALUE '2018'
FROM
    fhem.history acht
WHERE
    DEVICE LIKE 'Wasserverbrauch' 
    AND READING LIKE 'statStateMonthLast' 
    AND year(TIMESTAMP) = 2018

But how can I Join the values for 2019? Can someone point me to the right direction?

Thanks,
Ruediger

Upvotes: 1

Views: 167

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This is a variation of GMB's answer. But if you want the results in chronological order by month, then you need to include that in the query:

SELECT MONTHNAME(timestamp) as Monat,
       SUM(CASE WHEN YEAR(timestamp) = 2018 THEN value END) value_2018,
       SUM(CASE WHEN YEAR(timestamp) = 2019 THEN value END) as value_2019
FROM fhem.history acht
WHERE device = 'Wasserverbrauch' AND
      reading = 'statStateMonthLast' AND
      timestamp >= '2018-01-01' AND
      timestamp < '2020-01-01'
GROUP BY MONTHNAME(timestamp), MONTH(timestamp)
ORDER BY MONTH(timestamp);

Upvotes: 0

GMB
GMB

Reputation: 222702

You can do conditional aggregation:

SELECT
    MONTHNAME(timestamp) Monat,
    MAX(CASE WHEN timestamp >= '2018-01-01' AND timestamp < '2019-01-01' THEN value END) `2018`,
    MAX(CASE WHEN timestamp >= '2019-01-01' AND timestamp < '2020-01-01' THEN value END) `2019`
FROM fhem.history acht
WHERE
    device = 'Wasserverbrauch' 
    AND reading = 'statStateMonthLast' 
    AND timestamp >= '2018-01-01' 
    AND timestamp < '2020-01-01'
GROUP BY MONTHNAME(timestamp)

Notes:

  • the LIKE conditions in the WHERE clause are actually equivalent to =s, since there is no wildcard in the right operand; I changed them accordingly

  • it is usually a better practice to do explicit datetime range comparison rather than using date functions (in your case, year()), since the latter prevents the use of an index

  • if you need to handle multiple recors per month (which does not seem to be the case based on your existing query), then you should use another aggregate function to get a more sensible result (sum() or avg())

Upvotes: 1

artoodetoo
artoodetoo

Reputation: 938

You can use EXTRACT() function to get year/month from timestamp and conditional expression to put all in columns.
I think table with month numbers and names would be useful for outer join.

SELECT 
  m.`month`, 
  SUM(IF(h.`year_no`=2019, h.`value`, 0)) AS `2019`, 
  SUM(IF(h.`year_no`=2020, h.`value`, 0)) AS `2020`
FROM `monthes` AS m
LEFT JOIN (
  SELECT 
    EXTRACT(YEAR FROM `timestamp`) AS `year_no`,
    EXTRACT(MONTH FROM `timestamp`) AS `month_no`,
    `value`
  FROM `history`
) AS h ON h.`month_no` = m.`id`
GROUP BY m.`id`

I used GROUP BY month IDs, so result will be implicitely ordered by month number, not by month name.

https://www.db-fiddle.com/f/78LQtak6GwkDS8pzredroQ/0

I did NOT use additional filters here for bravity, but if you need them, add WHERE... into nested subquery right bellow FROM history.

Upvotes: 0

Related Questions