'Return counts of grouped record column that match condition

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?

postgresql


Solution 1:[1]

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.

Solution 2:[2]

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,
   wur.* 
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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions