I've created tables where is stored data like:

id  | name  |    date    | Price
==================================
1   | test1 | 2021-12-30 |  20.50
2   | test2 | 2021-12-01 |  30
3   | test3 | 2021-12-03 |  10.25

4   | test4 | 2021-12-06 |  15
5   | test5 | 2021-12-09 |  25

so example in 29.11 - 05.12 we have (let's call it week 1 because i dont know whats week is today), in 06.12 - 12.12 is week 2, 13.12 - 19.12 got week 3 and more...

I would like to get weekly sum prices (in example for week 1 we should got sum of 20.5 30 10.25, in week 2 - sum of 15 25) and make an echo depending on the particular day of the week we are in. If we are in week 1 = the sum for week 1 should be displayed to me, if in week 2 = the sum for week 2, and if in week X = the sum for week X

Like: "We are in 42 week, prices for this week is (SUM of dates in 42 week)" after sunday it will change for "WE are in 43 week, prcies for this week is (SUM of dates in 43 week".

I hope I have explained it well. Sorry for linguistic mistakes.

CodePudding user response：

In the following I assume that you want a week to start with a Monday as your examples show.

You can get the weekday of today with dayofweek(curdate()). It's 1 for a Sunday up to 7 for a Saturday.

To get the number of days to subtract from today to get the Monday of the current week you can use -((dayofweek(curdat()) 5) % 7). The following table shows that this calculation is right:

So to get the current week's Monday you can use adddate(curdate(), -((dayofweek(curdat()) 5) % 7)). To get next week's Monday you can use -((dayofweek(curdate()) 5) % 7) 7).

And with having current and next week's Mondays you can limit the days using a range in the WHERE clause giving you the records for the current week and sum their prices.

SELECT sum(price)
       FROM elbat
       WHERE date >= adddate(curdate(), -((dayofweek(curdat())   5) % 7))
             AND date < adddate(curdate(), -((dayofweek(curdate())   5) % 7)   7);

Note: You could also simply use yearweek(date) = yearweek(curdate()). Granted, that's simpler to read and grasp. But then, there'd be a function applied to date. And that prevents the use of an index on date and potentially makes the query slower. So a range is to be preferred.