# Query: All employee with sum of salary > 500

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

-

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.

-
@MartinSmith is it available in MySQL? –  Grijesh Chauhan Jan 24 '13 at 17:37
@grijeshChauhan updated my answer...hopefully that's more what you wanted. –  Derek Downey Jan 24 '13 at 18:26
Thanks Dtest its working and understood to me! –  Grijesh Chauhan Jan 26 '13 at 4:27
Yes slow but I am interested only for learning purpose :) I was trying to answer this qestion that time I wrote is question : thanks! –  Grijesh Chauhan Jan 26 '13 at 4:30
Derek Downey can you suggest me a book to improve my query skill. I mostly like to work in MySQL. –  Grijesh Chauhan Mar 21 '14 at 9:46

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;

-
I had something similar in my answer, but updated it to its current form to avoid the potential issue described in the docs of reading/writing from the same user variable and adhering to the recommendation "As a general rule, you should never assign a value to a user variable and read the value within the same statement." Like SELECT @a, @a:=@a+1 –  Derek Downey Jan 24 '13 at 18:54
@user1931858 thanks your answer, it is very helpful for me :) –  Grijesh Chauhan Jan 26 '13 at 4:32