"Sum(if) grouping in MySQL"

Question

I have the following table which is not loading: payment date, User ID, MonthlyAmount_last30d

User ID and payment date come from table. In the monthlyAmount_last30d field I want to see the amount for the period (payment date - 30 days).

I can't figure out how to group these columns correctly to get a result like this. If I only group by user_id, then I only see a first row with the payment date (I need all rows, each row has the payment total for that user id, the total is the payment for that row date - sum of 30 days). If I group by both fields -> the payment amount I get is always equal to the payment amount for that row and not the actual amount for the period I need.

select
order_id,
order_date,
sum( if( ( order_date <= order_date 
         and order_date >= order_date - interval 30 day), order_cost_rub, 0)) monthlyAmount_last30d,
用户身份，
order_cost_rub

from MyTable

group by
user_id

Thanks for any tips!

renew:

Data example:

user_id	Payment Date	order_cost_rub
1	2022-01-01 12:00:00	100
1	2022-01-01 13:00:00	100
1	2022-01-03 20:00:00	150
2	2022-01-03 16:00:00	200
2	2022-01-15 11:00:00	300

Expected results:

user_id	Payment Date	order_cost_rub	monthlyAmount_last30d
1	2022-01-01 12:00:00	100	100
1	2022-01-01 13:00:00	100	200
1	2022-01-03 20:00:00	150	350
2	2022-01-03 16:00:00	200	200
2	2022-01-15 11:00:00	300	500

Answer 1

I think what you are trying in IF() is wrong. I think what you are asking for should be applied in a WHERE clause like

I want all transaction records for everyone that occurred within the past 30 days. From that, I want the total of all their purchases, grouped by each person. Sounds right?

select
      user_id,
      payment_date,
      order_cost_rub,
      sum( order_cost_rub ) 
         OVER ( PARTITION BY user_id 
                order by payment_date ) AS monthlyAmount_last30d
   from 
      MyTable
   where
      order_date >= subdate(curdate(), interval 30 day)

By applying the WHERE clause of the query, you never get any previous transaction records, so there is no need to apply IF() conditional checks on each record.

Revised query. So, you want to show every record, but only looking back 30 days. So, the WHERE clause still applies. But now I am showing all columns for all normal records. Take a look at SUM(). This is doing the cumulative total you requested.

Note the OVER clause after it (I indented it just to show its context relative to the sum() procedure). OVER specifies partitioning (similar to group by, but not an ordinary external group by). This simply means restarting when this partition value changes. In this case, it's the user ID. And the data is sorted in payment_date order through the ORDER BY instruction within the partition.

I think this should meet your needs.