I’m trying to build a calculated value to count of some aggregation based on some field values.
I have a dataset with the properties “title” and “status” and I want to get the distinct count grouped by title where status=“PASSED” and there is no status=“FAILED”
distinct_countIf( title , status=“PASSED”) does not give me the correct answer as I don’t want to count the titles that also have status=“FAILED”.
The athena query that gives me to correct number of rows in the dataset is:
COUNT(CASE WHEN status=‘PASSED’ THEN 1 END) as PASSED,
COUNT(CASE WHEN status=‘FAILED’ THEN 1 END) as FAILED
group by title
having COUNT(CASE WHEN status=‘PASSED’ THEN 1 END) > 0 and COUNT(CASE WHEN status=‘FAILED’ THEN 1 END) = 0
Is there any way to achieve that through a calculated field?