Anand AK Anand AK - 3 months ago 10
SQL Question

SQL query employee database

I want to find the company that pays the least total salary along with total salary paid.

The details are as follows,

works( empid, companyname, salary )


And my query looks like,

SELECT COMPANYNAME
FROM WORKS
WHERE SALARY=
(SELECT MIN(SUM(SALARY))
FROM
(SELECT SUM(SALARY)
FROM WORKS
GROUP BY COMPANYNAME)
GROUP BY COMPANYNAME);


but this doesn't return any rows.

I am new to SQL so I hope you can reply to me in an easy to understand way.

Answer

Your query doesn't return any rows because you are looking for rows in your original table, works. In that table you don't have the total salary paid for any company, only the individual employee salaries, so of course the query will not return any rows. There isn't any individual employee whose salary equals the minimum TOTAL salary among companies.

There are many ways to solve this problem, but since you are a beginner, you should only look for solutions that use the basic SQL statements (even though you wouldn't use such a solution in production). Your goal right now is to learn how SQL works, not to get the fastest or most advanced solution.

With that said: First you will need to create a query that groups the rows by company and then choose the company (or the companies, if there is a tie for first place) with the lowest total salary. For this you need to use GROUP BY, as you already did. Then, to select from the GROUPS (not from the original rows), you need to use the HAVING clause, not the WHERE clause. Something like this:

select   companyname, sum(salary) as total_salary
from     works
group by companyname 
having   sum(salary) = ( select   min(sum(salary))
                         from     works
                         group by companyname
                       )
;

Note also that SQL allows you to wrap an aggregate function within another - you can write select min(sum(salary)) directly against the original table. This is illustrated in my subquery; compare to what you had in your original attempt.