multiple one table count with joined tables

[cicovec]

Hello, I'm new here and I'm looking for help from someone more skilled in SQL Queries building.

I've got this:

SELECT categories.category_id, categories.name, categories.parent, COUNT(links.link_id) AS countall
FROM categories LEFT JOIN links ON (categories.category_id = links.category_id)
GROUP BY categories.category_id, categories.name, categories.parent
ORDER BY categories.parent

This selects some columns from categories, and counts all records in links that corespond to the category_id.

The problem is, that I need to add one more column to this result set, containing number of records in links where links.category_id equals categories.category_id and links.active = 1.

Something like:
COUNT(links.link_id) AS countall WHERE links.active = 1 AND categories.category_id = links.category_id
it, also needs to correspond to the categories.category_id

I'm slowly getting desperate.
Thank you in advance for your answer.

 

[PHV]

Take a look at CASE ... WHEN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244