CODEWITHSUNDEEP
phpmysqlsql
simo
simo

Reputation: 79

SQL SUM group by having SUM

I've got this structure table:

USER_ID | POINTS_BALANCE

10     |  180          
10     |  20               
10     |  100                 
10     |  120

How can I UPDATE points_balance field for example to 500 for all this users:

select user_id, sum(points_balance)
from `user_points_table`
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499

Or can I INSERT a new ROW for user_id that having sum(points_balance) between 400 and 499?

EDIT:

For example:

select user_id, sum(points_balance)
from `user_points_table`
where user_id = 74
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499

Result:

USER_ID | POINTS_BALANCE
74      |  434 <-- How can I update this result to 500?

Upvotes: 0

Views: 414

Answers (3)

Denis Alexandrov
Denis Alexandrov

Reputation: 185

UPDATED

As I understand your question, you are looking for a way to set every users points to minimum of 500 by adding the difference. So, your solution might look like the following:

mysql> DELIMITER //;
-> CREATE PROCEDURE updateMinimum()
-> BEGIN
-> DECLARE u,s INT;
-> DECLARE cur CURSOR FOR
-> SELECT user_id, SUM(points) FROM user_points GROUP BY user_id;
-> OPEN cur;
-> read_loop: LOOP
->    FETCH cur INTO u,s;
->    IF s < 500 THEN
->        INSERT INTO user_points (user_id, points) VALUES (u, 500-s);
->    END IF;
-> END LOOP;
-> CLOSE cur;
-> END//
mysql> DELIMITER ;
mysql> CALL updateMinimum();

It will tell you there's No data, but don't mind - it did the trick. Just use SELECT to check it out:

SELECT user_id, SUM(points) FROM user_points GROUP BY user_id;

You may use CALL updateMinimum() whenever you want, anytime. Or if you don't need it anymore you can remove it by DROP PROCEDURE updateMinimum();

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

Since you want to add new a new row to the table, one option is to use insert into select:

insert into user_points_table 
select user_id, 500-sum(points_balance)
from `user_points_table`
where user_id = 74
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499;

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37129

You cannot update the information you derived. If you are looking to display a different number from what you derived, you can do this:

select 
  user_id, 
  case 
  when totals < 500 then 500 
  else totals 
  end as totals 
from (
  select user_id, sum(points_balance) as totals 
  from test 
  -- where user_id = 10
  group by user_id 
  having totals between 400 and 499
) t
-- where user_id = 10;

+---------+--------+
| user_id | totals |
+---------+--------+
|      10 |    500 |
+---------+--------+

If the result of 

select user_id, sum(points_balance) as totals 
from test 
-- where user_id = 10
group by user_id 
having totals between 400 and 499;

is 

+---------+--------+
| user_id | totals |
+---------+--------+
|      10 |    420 |
+---------+--------+

and you want to bump this up to 500, you know that the difference between 500 and 420 is 80. So, insert a new record:

insert into test (user_id, points_balance) values (10, 80);

or you can update one of the records to:

update test set points_balance = points_balance + 80
where user_id = 10 and points_balance = 120;

The question follows - which of the many records for 10 will you update? I hope you have an id to uniquely identify each row. If you have that, update the row with lowest id. If you don't have a field that uniquely identifies each row, this would be a good time to create one such field.

In order to do this all in one shot, you would build a stored procedure or write a script in PHP/Python/whatever language you feel comfortable.

Upvotes: 0

Related Questions