Count rows where field is NULL

[paradoxperfect]

How can I ONLY count rows where a certain field is null?
Here's my statement so far:

$query = "SELECT assets.id, assets.ast_serial_num, assets.ast_image_id, assets.ast_history, assets.ast_asset_tag, users.usr_name, COUNT(users.usr_name) FROM ((assets LEFT JOIN rel_ua ON rel_ua.rel_asset_tag = assets.id) LEFT JOIN users ON rel_ua.rel_login_id = users.id AND rel_ua.date_returned is NULL ) GROUP BY assets.ast_asset_tag";


In the table rel_ua it stores the id of the users and assets in relation to how they are assigned along with a returned date
Ex:
usr_id | asset_id | returned_date
1 | 1 | NULL
2 | 1 | NULL
1 | 2 | 2007-01-01
3 | 2 | NULL
1 | 3 | NULL

But if I do the above query it'll count the users assigned to asset 2 and think that it's TWO users... when I just want the ones that have NULL. Like if it was on the asset id 3, it would only display the 1 user...

I want to have a statement where it'd kind of be like COUNT(users.usr_name WHERE rel_ua.returned_date is NULL) but I don't that it's valid...


This is in relation to my other thread... but I've sort of solved some of the problems. If you need more code (like what it's supposed to be outputting etc) let me know. :)

Thanks!

[paradoxperfect]

Nevermind... I think this query fixed it:
$query = SELECT assets.id, assets.ast_serial_num,assets.ast_image_id,assets.ast_history,assets.ast_asset_tag, users.usr_name,
COUNT(users.usr_name) FROM ((assets LEFT JOIN rel_ua ON rel_ua.rel_asset_tag = assets.id AND rel_ua.date_returned is NULL) LEFT JOIN users ON rel_ua.rel_login_id = users.id ) GROUP BY assets.ast_asset_tag;

Funny, lately I've been posting topics and then about 15 minutes later... I'll solve the problem on my own ha! :D

[Sheldon]

Just keep posting the fixed solutions so sthat others can learn from what you are doing.

Remeber this is a learning community so you are never posting stupid questions, their only stupid if no one ever knows them ( CHAZZY)

[paradoxperfect]

:) Will do!!