Count value if meets criteria

[Hayyel]

Hello again,

If I have an array obtained by a mysql query with a WHERE clause how do I get it to return 0 where there is no match?

For example:
Table contains:

Name: Age: Job:
John 21 Cashier
Adam 15 Bagger
Susie 42 Manager
Jill 19 Cashier
Mark 36 Manager

My Query is this:

$query = "SELECT job, COUNT(*) FROM table WHERE age <= 35 GROUP by job ";
$result = mysql_query($query) or die(mysql_error());
$i=0;
while ($array=mysql_fetch_assoc($result)) {
$datay[$i]=$array;
$i++;
};
Result:

Array
(
[0] => Array
(
[job] => Bagger
[COUNT(*)] => 1
)

[1] => Array
(
[job] => Cashier
[COUNT(*)] => 2
)
)

I need the result to include the jobs that do not fit the WHERE and give them a count of zero.



Array
(
[0] => Array
(
[job] => Bagger
[COUNT(*)] => 1
)

[1] => Array
(
[job] => Cashier
[COUNT(*)] => 2
)
[2] => Array
(
[job] => Manager
[COUNT(*)] => 0
)

I only want the rows that meet the criteria counted.

[Old Pedant]

SELECT job, SUM( IF(age <= 35, 1, 0 ) ) AS thecount
FROM table
GROUP BY job
ORDER BY job


There are other ways to do this, but this is the simplest.

Oh, w.t.h. One other way, just as an example:

SELECT DISTINCT T.job, S.thecount
FROM table AS T
LEFT JOIN ( SELECT T.job, COUNT(*) AS thecount
FROM table
GROUP BY job ) AS S
ON T.job = S.job
ORDER BY T.job


I think you can see why I say the first method is the simplest.
I *think* it should also be somewhat faster.

[Hayyel]

That is exactly what I needed. Thank you very much.

On a side note looking at the second example I realised how much more I still have to learn. =)