CODEWITHSUNDEEP
sql
D.Khumalo
D.Khumalo

Reputation: 47

SQL Query Predicament

I need to create an sql query which retrieves the sum of commission and salary from an agent that is less than the salary of the lowest earning manager

Here is my query so far I am receiving a blank table:

SELECT AgentName, 
       Salary,
       Commission,
       (SELECT sum(Premium) 
        From Policy,PolicyCover 
        WHERE AgentNo IN ( Select AgentNo From Policy)) As [Total Premium]
FROM Agent,Policy,PolicyCover
WHERE (Select sum(Salary+Commission) 
       From Agent) <
      (Select Min(Salary) 
       From 
       Agent Where Function= "Manager")

Upvotes: 0

Views: 164

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

You are a beginner, so I think it would not help that much if I showed you a prêt-à-porter query. Instead I'll tell you what your query does.

You are using an out-dated join syntax. You can replace the comma between the table name by the explicit keywords CROSS JOIN. So your main query has:

FROM Agent, Policy, PolicyCover

which translates to

FROM Agent CROSS JOIN Policy CROSS JOIN PolicyCover

and means: Take every record from Agent and combine them with every record from Policy and then take the resulting rows and combine them with every record in PolicyCover. Lets say Agent has 100 records, Policy 200 and PolicyCover 300. That makes 100 x 200 x 300 combination = 6,000,000 rows.

Now to the WHERE clause:

WHERE (Select sum(Salary+Commission) From Agent) <
      (Select Min(Salary) From Agent Where Function = "Manager")

So you select the sum of salary plus commison from Agent. Then you select a minimum salary from Agent, which is of course less then the sum of all salaries (plus commissions). So the condition is never met. You select no records.

If you had a WHERE clause where the condition were met, you'd select some columns from the myriads of combined rows plus a sum of premium values. Let's look at that subquery: You cross join Policy and PolicyCover , thus creating all combinations. In WHERE you make sure that the AgentNo you got from the Policy table exists in the Policy table. Well, this is always true of course.

First thing: Learn proper joins (INNER JOIN ... ON ... to start with). Think about what relates the tables and only join related records (e.g. with the same policy number).

Next thing: make it a habit to qualify your columns when working with more than one table. Is Premium for instance a column of Policy or PolicyCover? Make this obvious by specifying the table name: SUM(PolicyCover.Premium). Or use a table alias: FROM PolicyCover pc and SUM(pc.Premium).

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Is it not as simple as this?

select (salary + commission) "sum of commission and salary"
from agent
where (salary + commission) < (select min(salary) from agent where function = 'Manager')

Upvotes: 0

Related Questions