How to show all 3 urls are null in following table in postgresql/sql-CodePudding

I want to show records where url is null and typeurl of type1 & type2 also null. Output should be productname : Oil

ProductName     Url    Type     TypeUrl
Shampoo         null   Type1    null
Shampoo         null   Type2    www.domain.com
Conditioner     null   Type1    null
Conditioner     null   Type2    www.domain.com
Oil             null   Type1    null
Oil             null   Type2    null

postgresql query I want

CodePudding user response：

We can use a simple aggregation here:

SELECT ProductName
FROM yourTable
GROUP BY ProductName
HAVING COUNT(Url) = 0 AND
       COUNT(TypeUrl) FILTER (WHERE Type IN ('Type1', 'Type2')) = 0;

For versions of Postgres which do not support FILTER, use this version:

SELECT ProductName
FROM yourTable
GROUP BY ProductName
HAVING COUNT(Url) = 0 AND
       COUNT(CASE WHEN Type IN ('Type1', 'Type2') THEN TypeUrl END) = 0;