CODEWITHSUNDEEP
mysqlsql
Eric C
Eric C

Reputation: 1

Trouble with sql COUNT

Having some trouble with sql, Iv been trying to figure out how to work this out

"For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5"

My code right now finds all the callers with less than 5 calls, however doesnt find callers with 0 calls?. also each company is assigned 1 contact which should be displayed beside the total number calls under 5 for that company.

I realise i probably haven't explained it well enough but if you look on https://sqlzoo.net/wiki/Helpdesk_Medium_Questions (Question 8) you cant find the question and see how all the tables link and the correct output.

Any help is very much appreciated. Thanks

SELECT Company_name, First_name, Last_name, COUNT (Company_name) as nc
FROM Customer 
JOIN Caller ON Customer.Company_ref = Caller.Company_ref 
JOIN Issue ON Caller.Caller_id = Issue.Caller_id 
WHERE Company_name in 
(SELECT Company_name FROM Customer 
JOIN Caller ON Customer.Company_ref = Caller.Company_ref
JOIN Issue ON Caller.Caller_id = Issue.Caller_id
GROUP BY Company_name
HAVING COUNT(Company_name) < 5) 
GROUP BY Company_name, First_name, Last_name

Upvotes: 0

Views: 71

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

for your request you should use count(*) and not conut(company_name)

  SELECT Company_name, First_name, Last_name, COUNT (*) as nc
  FROM Customer 
  JOIN Caller ON Customer.Company_ref = Caller.Company_ref 
  JOIN Issue ON Caller.Caller_id = Issue.Caller_id 
  GROUP BY Company_name, First_name, Last_name 
  HAVING COUNT (*) < 5

Upvotes: 1

Related Questions