1

I would like some guidance on how to write the SQL for the following?

Lets say I have the following table:

Employee
EmpNo | EmpName | EmpCommRate
1 | John  | 0.020
2 | Lewis | 0.040
3 | Bobby | 0.030
4 | Pete  | 0.050

Orders
OrdNo | EmpNo
101 | 1
102 | 1
103 | 4
104 | 2
105 | 3
106 | 3
107 | 1

For each employee with a commission less than 0.040, compute the number of orders taken. The result should include the employee number, employee name, employee commission rate, and the total number of orders taken by that employee. Assume each distinct OrdNo in the Orders counts as one distinct order. Each employee should be displayed only once; for example, if employee Mickey took five orders, there should be one line displaying his name, not five.

Output should be:

EmoNo | EmpName | EmpCommRate | Total Orders
1 | John  | 0.020 | 3
3 | Bobby | 0.030 | 2

What would be the best SQL command to output the above?

IronBat
  • 107
  • 2
  • 10
  • What is the SQL you have tried with. You should always at least try. Then tell us what is not working, if anything. – trincot Mar 06 '16 at 16:18

2 Answers2

-1

try this

SELECT e.EmpNo, EmpName, EmpCommRate, count(*) AS 'Total Orders'
FROM  Employee e, Orders o
WHERE e.EmpNo = o.EmpNo
AND EmpCommRate < 0.040
GROUP BY e.EmpNo, EmpName, EmpCommRate
SevenOfNine
  • 630
  • 1
  • 6
  • 25
  • 1
    Never ever teach new people to use IMPLICIT join syntax! always use the proper join syntax. – sagi Mar 06 '16 at 16:21
  • why is that a problem? There is a reason there are these two possibilities. This solution is more intuitive in my eyes. Furthermore he did not ask to teach him but to give him the 'best SQL command'. There is also a discussion about this here: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – SevenOfNine Mar 06 '16 at 17:33
  • Implicit join syntax will surely lead to mistakes when joining multiple tables. SQL beginners should make it a habbit to use only explicit join syntax . – sagi Mar 06 '16 at 23:16
  • Please elaborate on this answer with an explanation. – theMayer Mar 07 '16 at 14:56
-1

What you need is a join and a group by clause, like this:

SELECT t.empNo,t.empName,t.empCommRate,count(distinct s.ordNo) as Total
FROM Employee t
INNER JOIN Orders s
 ON(t.empNo = s.empNo)
WHERE t.empCommRate < 0.040
GROUP BY t.empNo,t.empName,t.empCommRate

It can also be done with a correlated query like this:

SELECT t.empNo,t.empName,t.empCommRate,
       (select count(distinct s.ordNo) from orders s
        where s.empno = t.empno) as Total
FROM Employee t
WHERE t.empCommRate < 0.040
sagi
  • 40,026
  • 6
  • 59
  • 84