Question 297192 in Mysql Question posted in
The official MySQL documentation can be found here.

How to Calculate Total Price Per Month in MySQL

I would like to calculate total Prise per month.

i have a database where I save data every minute.

TIME                    KWHI        EUR
2023-12-02 23:00:15     8852.892    12.8040
2023-12-02 22:59:13     8852.892    13.4496
2023-12-02 22:58:11     8852.892    13.4496
2023-12-02 22:57:09     8852.892    13.4496

SELECT  TIME,
     ((max(KWHI) - min(KWHI)) * EUR)/100 AS HOUREUR
FROM LOG WHERE 1    
GROUP BY 
    YEAR(TIME),
    MONTH(TIME),
    DAY(TIME),
    HOUR(TIME)
ORDER BY TIME DESC LIMIT 24

in this query i get amount of one hour

the price EUR changes every hour

I would like to calculate by one month?

Tags:

2

Answers


  1. 0

    select coalesce(sum(e.amount), 0) as expense, m.month
    from (select ’01’ as month union all
    select ’02’ as month union all
    select ’03’ as month union all
    select ’04’ as month union all
    select ’05’ as month union all
    select ’06’ as month union all
    select ’07’ as month union all
    select ’08’ as month union all
    select ’09’ as month union all
    select ’10’ as month union all
    select ’11’ as month union all
    select ’12’ as month
    ) m left join
    expenses e
    on year(e.date) = 2019 and
    DATE_FORMAT(e.date,’%m’) = m.month
    group by m.month;
    Here is a db<>fiddle.

    Login or Signup to reply.
  2. 0

    You could do this by wrapping your existing query in an outer SELECT that aggregates over a month:

    SELECT
    DATE_FORMAT(MAX(TIME), '%Y-%m') AS 'MONTH',
    SUM(HOUREUR) AS 'MONTHEUR'
FROM (
    SELECT MAX(TIME) AS TIME,
        ((max(KWHI) - min(KWHI)) * MAX(EUR))/100 AS HOUREUR
    FROM LOG
    WHERE 1    
    GROUP BY 
        YEAR(TIME),
        MONTH(TIME),
        DAY(TIME),
        HOUR(TIME)
) hourly
GROUP BY 
    YEAR(TIME),
    MONTH(TIME)
ORDER BY 1 DESC

    If you want to account for changes in KWHI between the last reading of one hour and the first reading of the next, I would propose a slighly simpler calculation. Instead of using max(KWHI) - min(KWHI) over a group, I suggest using a window function per row. That then means it easier to group over any time range you need.

    SELECT
    DATE_FORMAT(MAX(logsums.TIME), '%Y-%m') AS 'MONTH',
    SUM(logsums.EURSUM) AS 'EURSUM'
FROM (
    SELECT
        TIME,
        EUR * (KWHI - LEAD(KWHI) OVER (ORDER BY TIME DESC)) / 100 AS 'EURSUM'
    FROM LOG
) logsums
GROUP BY
    YEAR(logsums.TIME),
    MONTH(logsums.TIME)
ORDER BY 1 DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search