Query: All employee with sum of salary > 500

Question

In my MYSQL Database COMPANY, I have a Table: Worker :

mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SSN    | varchar(64) | NO   |     | NULL    |       |
| name   | varchar(64) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)  

I created using following MySQL's command:

CREATE TABLE `Worker` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;  

The table having following data:

mysql> SELECT * FROM `Worker` ORDER BY salary ASC;
+-----+---------+--------+
| SSN | name    | salary |
+-----+---------+--------+
| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |
| 2   | Rahul   |    300 |
| 4   | Harsh   |    500 |
| 7   | THREE   |   1000 |
+-----+---------+--------+
7 rows in set (0.00 sec)  

its insert query:

INSERT INTO `Worker`
    (SSN, name, salary) 
  VALUES 
    ("1", "Grijesh", 200),
    ('2','Rahul',300),
    ('3','Sumit',250),
    ('4','Harsh',500),
    ('5','ONE','100'),
    ('6','TWO',50),
    ('7','THREE',1000);  

[QUESTION]

I wants to select first n workers (in asc list)those sum of salary is just more than 500 (> 500). for example:

following four workers are first low salary employees and having SUM of salary 600. (600 > 500)

| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |    

I need something like SELECT * FROMWorkerWHERE SUM(salary) < 501 ASC; (its incorrect query)

An assumption can be made that all having different salary.

_{its something like cost cutting: remove employee those are relatively unimportant}

A suggestion or link to read will be helpful

Answer 1

After playing around with this some more, I came up with this query:

SELECT ssn, name, salary FROM (
  SELECT ssn, name, salary, (@total:=@total+salary) as total
  FROM worker, (select @total:=0) t
  ORDER BY salary ASC
) as foo
WHERE (total-salary) <=500

This seems to do what you want on this limited data, but could be very slow on large datasets.

Answer 2

Here is one solution, but it looks ugly. There may be a better solution without a subquery.

SELECT ssn, name, salary, prev_accum, accum from
(
    SELECT *, @a AS prev_accum, @a:= @a + salary AS accum 
    FROM Worker, (select @a :=0) as dummy
    ORDER BY salary
) AS dummy2
WHERE prev_accum < 500;

If you run this query in phpmyadmin, there is a display error, which shows BLOB for variable. You will need the following casting.

SELECT ssn, name, salary, CAST(prev_accum as decimal) prev_accum, accum from
(
    SELECT *, @a AS prev_accum, @a:= @a + salary AS accum 
    FROM Worker, (select @a :=0) as dummy
    ORDER BY salary
) AS dummy2
WHERE prev_accum < 500;