CODEWITHSUNDEEP
mysqlsqlselectgroup-byleft-join
Yodism
Yodism

Reputation: 247

COUNT() and Left Join not working

I'm having trouble at query which displays the Employee Number, First Name, Last Name and the number of orders of each employee. Make sure that the Number of orders column name should be “OrderCount”. Order By the OrderCount then by employee ID. Null is allowed.

employee_data containing columns: Emp_ID, F_Name, L_Name

order_data containing columns: O_ID,Emp_ID, OrderNumber

Here's my query:

Select order_data.Emp_ID, F_Name, L_Name, COUNT(employee_data.Emp_ID) as OrderCount
FROM order_data
LEFT JOIN employee_data
ON employee_data.Emp_ID = order_data.Emp_ID
GROUP BY order_data.Emp_ID
ORDER BY OrderCount

These are my tables:

employee_data enter image description here

order_data

enter image description here

And the output should be:

enter image description here

But it is giving me this wrong input.

enter image description here

Upvotes: 2

Views: 1347

Answers (6)

pradip vaghani
pradip vaghani

Reputation: 182

Replace in your Query

ORDER BY OrderCount

To

ORDER BY order_data.Emp_ID

Your Problem Solved....

Upvotes: 2

Olli
Olli

Reputation: 1738

As others said, if you don't wanna have the NULL values and don't want to know the employees with no orders, you could use a join... if you need the orders for non existing employees (however this may work), use

SELECT
  od.Emp_ID, ed.F_Name, ed.L_Name, COUNT(od.Emp_ID) as OrderCount
FROM 
  order_data od
LEFT JOIN 
  employee_data ed ON ed.Emp_ID = od.Emp_ID
GROUP BY 
  od.Emp_ID
ORDER BY 
  OrderCount, od.Emp_ID

you were also missing the second ORDER BY value.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT od.Emp_ID, ed.F_Name, ed.L_Name, COUNT(DISTINCT od.O_ID) AS OrderCount
FROM order_data od 
LEFT JOIN employee_data ed ON ed.Emp_ID = od.Emp_ID
GROUP BY od.Emp_ID
ORDER BY OrderCount

Upvotes: 2

Tarun Gaba
Tarun Gaba

Reputation: 1113

Aggregate functions cannot be used along with other fields in a query. You can use it in a sub-query, as follows:

Select order_data.Emp_ID, F_Name, L_Name, (select COUNT(employee_data.Emp_ID) from employee_data) as OrderCount
FROM order_data
LEFT JOIN employee_data
ON employee_data.Emp_ID = order_data.Emp_ID
GROUP BY order_data.Emp_ID
ORDER BY OrderCount

Hope it helps .. :)

Upvotes: 2

G.S Abhaypal
G.S Abhaypal

Reputation: 322

You must declare the columns in the group by clause which are not being used in any aggregate function. Try to add 'order_data.Emp_ID, F_Name, L_Name' these columns in group by clause.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I think you have the tables in the wrong order:

Select ed.Emp_ID, ed.F_Name, ed.L_Name, COUNT(od.Emp_ID) as OrderCount
FROM employee_data ed LEFT JOIN
     order_data od
     ON ed.Emp_ID = od.Emp_ID
GROUP BY ed.Emp_ID
ORDER BY OrderCount;

Presumably you want all employees, with counts of their employees. In a properly maintained database, you shouldn't have invalid employee ids on the order table.

Upvotes: 0

Related Questions