CODEWITHSUNDEEP
sqlsql-server
Willian
Willian

Reputation: 95

Select Categories and count the Ads inside

I want to make a query that select Categories name and count the max of Ads (active) in each category, I already did this:

SELECT
cat.cat_id,
cat.cat_desc,
COUNT(ad_id) AS cat_total_ads
FROM 
tab_categories AS cat LEFT JOIN
tab_ads AS ads ON ads.cat_id = cat.cat_id
WHERE
cat_active = @cat_active AND
cat_deleted = @cat_deleted
GROUP BY
cat_desc, cat.cat_id

This query is counting the non active Ads too. How can I put the value 0 for the non active Ads?

Upvotes: 2

Views: 90

Answers (2)

Jack
Jack

Reputation: 11003

One way to accomplish this is to use a combination of a CASE statement to determine if you want to count the item and the SUM function.

For example 

SUM(CASE WHEN Active THEN 1 ELSE 0 END)

Upvotes: 3

Don Thomas Boyle
Don Thomas Boyle

Reputation: 3045

The SUM ( CASE ..... then 1 else 0 end ) as colName should do it for you !!

Try this i do not have the table to recreate to test but this or something similar should work.

select 
  cat.cat_id,
  cat.cat_desc,
  count(ad_id) as Total,
  sum(case when ad_id= 0 then 1 else 0 end) as 'ad_id'

from
  tab_categoriesAS cat LEFT JOIN
tab_ads AS ads ON ads.cat_id = cat.cat_id
WHERE
cat_active = @cat_active AND
cat_deleted = @cat_deleted
GROUP BY
cat_desc, cat.cat_id

Upvotes: 1

Related Questions