SQL - Sum last value by date of current week-CodePudding

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

CodePudding user response：

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;

CodePudding user response：

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