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

SQL – Sum last value by date of current week – Mysql

I need to do a SUM of the last value (by date) of each day of the current week. To test I have a table with the values:

5 for 2023-01-12 16:53:01
2 for 2023-01-12 08:49:03
5 for 2023-01-11 08:58:19

I expect a result of 10.

I’m trying with the following code:

SELECT SUM(value) AS weeklyValue
        FROM (
            SELECT value
            FROM table
            WHERE WEEK(DATE(DataOra)) = WEEK(NOW())
            AND WEEKDAY(DATE(DataOra)) >= 1
            AND WEEKDAY(DATE(DataOra)) <= 7
            AND DataOra = (SELECT MAX(DataOra) FROM table WHERE WEEKDAY(DataOra) = WEEKDAY(DATE(DataOra)) )
            GROUP BY WEEKDAY(DATE(DataOra))
        ) AS subquery;

but the result is 5. Where is the mistake? Thanks

Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    Maybe I found a solution:

    SELECT SUM(Value) AS energiaSettimanale
        FROM (
            SELECT MAX(Value) as value, WEEKDAY(DataOra) as d
            FROM table
            WHERE WEEK(DATE(DataOra)) = WEEK(NOW())
            AND WEEKDAY(DATE(DataOra)) >= 0
            AND WEEKDAY(DATE(DataOra)) <= 6
            GROUP BY d
        ) AS subquery;

  2. 0

    You can do it using inner join as follows :

    select weekNumber, sum(value) from (
  select t.value, week(s.maxDataOra) as weekNumber
  from _table t
  inner join (
    select MAX(DataOra) as maxDataOra
    from _table
    group by DATE(DataOra)
  ) as s on s.maxDataOra = t.DataOra
) as b
group by weekNumber;

    Check it here : https://dbfiddle.uk/hadzywwh

    Login or Signup to reply.
  3. 0

    If I really understood what you want, this request may be (if I understood correctly, I am specifying) the solution

    select sum(value) from `table` 
inner join ( 
 select max(DataOra) as maxDataOra 
 from `table` 
 where WEEK(DATE(DataOra))=WEEK(NOW()) 
 group by WEEKDAY(DataOra)  
) 
as tmp on DataOra=tmp.maxDataOra

    The trick to find the most recent date of the day being to group by day number with the max() function on dateTime Data.

    Login or Signup to reply.
  4. 0

    I need to do a SUM of the last value (by date) of each day of the current week.

    So, let’s start by constraining our working set to just the current week’s data –

    WHERE `DataOra` >= (CURRENT_DATE - INTERVAL(WEEKDAY(CURRENT_DATE)) DAY) /* first day of current week (Mon - Sun) */
AND `DataOra` < (CURRENT_DATE - INTERVAL(WEEKDAY(CURRENT_DATE)) DAY + INTERVAL 1 WEEK) /* first day of next week (Mon - Sun) */

    Then we can use that to constrain the query used to get the MAX(DataOra) per DATE(DataOra) and join back to the original dataset to get the sum of the respective Values –

    SELECT SUM(`Value`) `energiaSettimanale`
FROM `table` `t1`
INNER JOIN (
    SELECT MAX(`DataOra`) AS `maxDataOra`
    FROM `table`
    WHERE `DataOra` >= (CURRENT_DATE - INTERVAL(WEEKDAY(CURRENT_DATE)) DAY) /* first day of current week (Mon - Sun) */
    AND `DataOra` < (CURRENT_DATE - INTERVAL(WEEKDAY(CURRENT_DATE)) DAY + INTERVAL 1 WEEK) /* first day of next week (Mon - Sun) */
    GROUP BY DATE(`DataOra`)
) `t2` ON `t2`.`maxDataOra` = `t1`.`DataOra`;

    Or if using MySQL >= 8.0 you could use the ROW_NUMBER() window function –

    SELECT SUM(`Value`) `energiaSettimanale`
FROM (
    SELECT `Value`, ROW_NUMBER() OVER (PARTITION BY DATE(`DataOra`) ORDER BY `DataOra` DESC) `rn`
    FROM `table`
    WHERE `DataOra` >= (CURRENT_DATE - INTERVAL(WEEKDAY(CURRENT_DATE)) DAY) /* first day of current week (Mon - Sun) */
    AND `DataOra` < (CURRENT_DATE - INTERVAL(WEEKDAY(CURRENT_DATE)) DAY + INTERVAL 1 WEEK) /* first day of next week (Mon - Sun) */
) `t`
WHERE `rn` = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search