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
);