[Resolved] Group by, count and still show entries with zero count

[petiejoe]

I am working with two tables. 'problemtype' simply has an id and a description, and 'phonetracker' includes a column 'problem_type_id' that refers to the id listed in 'problemtype.' I want to get the number of times that an id is listed.

I currently have the MySQL query like this:

SELECT t.description, count(*) AS Number
FROM phonetracker p, problemtype t
WHERE p.problem_type_id = t.id
GROUP BY p.problem_type_id
ORDER BY Number DESC

This returns everything correctly, but it does not return 'problemtype's that have zero listings in 'phonetracker' (for good reason, since those would obviously not be listed in the GROUP BY).

The question I have is if it would be possible to do some kind of join with the results of this query and the entire list in 'problemtype,' resulting in a list that has an accurate count of all the descriptions in 'problemtype.' I could easily do it by having two separate queries and combining the arrays in PHP, but I was curious if anybody knew of a more elegant method that just uses one MySQL query.

Thanks guys!

[tsinka]

Hi,

try this query:

SELECT t.description, count(p.problem_type_id) AS Number
FROM problemtype t LEFT JOIN phonetracker p
ON t.id = p.problem_type_id
GROUP BY t.id
ORDER BY Number DESC

Thomas

[petiejoe]

Thanks! Works like a charm, now I'll go figure out why it works...