Using multiple HAVING with multiple WHERE statement PSQL-CodePudding

I have a psql query where multiple WHERE clauses are being used in groupings (with brackets)

SELECT * 
FROM customers
WHERE (location = 'NYC' OR location = 'BK')
  AND (age > 18)

Notice that I have 2 conditions together which are grouped using brackets.

Now I'm trying to use the COUNT function to get another data from my dataset and I'm currently doing it using the HAVING statement. The problem is that it seems that there is no way to use HAVING multiple times (in each brackets) and should rather be used only once after the AND or do something like this HAVING x and y etc.. but this is not what I want.

I need to use HAVING in each brackets like this:

SELECT customers.id as id 
FROM customers
INNER JOIN friends ON friends.id = customers.id
WHERE (location = 'NYC' OR location = 'BK'
       GROUP BY customers.id
       HAVING COUNT(friends.id) > 2)
  AND (age > 18
       GROUP BY customers.id
       HAVING COUNT(friends.id) > 10);

So essentially (customers who live in NYC or BK having 2 friends) AND (customers 18 yos having more that 10 friends)

CodePudding user response：

How about splitting the SQL query into 2 parts, and use UNION on both?

CodePudding user response：

SELECT c.id
FROM customers c
    INNER JOIN friends f ON f.id = c.id
WHERE
    c.location = ('NYC', 'BK') or c.age > 18
GROUP BY c.id
HAVING COUNT(*) >
    case when min(c.location) in ('NYC', 'BK') then 2
         when min(c.age) > 18 then 10 end

When the conditions overlap it's not clear which requirement applies. If you actually want more than the id back use dummy aggregates or look into count(*) over (partition by.id).

CodePudding user response：

It's possible to use CASE WHEN in a COUNT.
And use that in the HAVING clause.

SELECT customer.id 
FROM customers AS customer
INNER JOIN friends AS friend
  ON friend.id = customer.id
GROUP BY customer.id
HAVING (   
   COUNT(CASE WHEN friend.location IN ('NYC','BK') 
         THEN friend.id END) > 2
   AND
   COUNT(CASE WHEN friend.age > 18 
         THEN friend.id END) > 10
);