MySQL return values count

Question

I have a fairly boggling problem. What I'm after is a list of all the industries whether they have been linked to a job or not. So..

Web Design [0]
Accounts[3]
Sales[1]
Marketing[0]

So that the jobs in the database have a industry id that's saved with the job. the industry table has a list of industry types. Web Design, Accounts, Sales, Marketing etc Below is my SQL so far

SELECT ind.name,ind.id, GROUP_CONCAT(job.industry_id) AS id,
COUNT(*) AS industry_count
FROM jobs AS job,
industries AS ind
WHERE ind.id = job.industry_id
GROUP BY industry_id

This returns the count of each job that belongs to an industry but i want all industries to be returned with or without jobs linked to them. Thank you all so much for helping. Rob

score 0 · Accepted Answer · answered Nov 09 '13 at 18:49

0

It seems you need a LEFT JOIN. Try this out:

SELECT ind.name, ind.id,
  COALESCE(GROUP_CONCAT(job.industry_id), 'default_value') AS id,
  COUNT(job.industry_id) AS industry_count
FROM industries ind
LEFT JOIN jobs job ON ind.id = job.industry_id
GROUP BY ind.id

Note if there is no job for a given industry you'll get null in the GROUP_CONCAT. You can add a default value for those cases that way.

answered Nov 09 '13 at 18:49

Mosty Mostacho

42,742
16
96
123

Yes, thanks. But now the count is returning 1 for all the null entries. What am i missing. thank you for your help – bobcoder Nov 09 '13 at 18:52
Yes, so we get...for example sales [0] – bobcoder Nov 09 '13 at 18:54
1

You are a star. Thank you so much. Have a great weekend – bobcoder Nov 09 '13 at 18:56

MySQL return values count

1 Answers1