Return counts of grouped record column that match condition-CodePudding

I have a table called watched_url_scan_result and it has a column watched_url_scan_status.

It;s purpose is to store scan results for each entry in the watched_url_record table.

What I want to do is create a query that gives me a count of each type of status appearing in the watched_url_scan_status for each record in the watched_url_record table.

I tried:

select count(wusr.watched_url_scan_status = 'NotFound') NotFound, count(wusr.watched_url_scan_status = 'Found') Found, * from watched_url_scan_result wusr
join watched_url_record wur on wusr.watched_url_record_id = wur.id
where wur.user_auth_custom_id = 4
group by wusr.id, wur.id

But it always shows the count as 1 regardless of what string I try to match the status to. I know I am way off on my approach, can someone point me in right direction please?

CodePudding user response：

count counts all the not NULLs. 'true' and 'false' are both not NULL.

You could apply a FILTER:

select 
   count(*) filter (where wusr.watched_url_scan_status = 'NotFound') NotFound, 
   count(*) filter (where wusr.watched_url_scan_status = 'Found') Found,
   * 
from watched_url_scan_result wusr
join watched_url_record wur on wusr.watched_url_record_id = wur.id
where wur.user_auth_custom_id = 4
group by wusr.id, wur.id

CodePudding user response：

Try this query:

select wur.id, count(wusr.watched_url_scan_status = 'NotFound') NotFound, count(wusr.watched_url_scan_status = 'Found') Found
from watched_url_scan_result wusr
join watched_url_record wur on wusr.watched_url_record_id = wur.id
where wur.user_auth_custom_id = 4
group by wur.id

You can add a HAVING clause if you wish to only show rows for NotFound and Found.