How to write SQL query with multiple conditions using CASE function

Question

The table The task is: Count the number of customers who simultaneously:

have more than 5 payments that exeed 5000 dollars
and have an average payment value of more than 10,000 dollars

I have done it using window function and subquery:

CREATE TABLE Customers (
client INT,
payment INT);

INSERT INTO Customers(client, payment) VALUES
(1, 1000),
(1, 7000),
(1, 6000),
(1, 50000),
(1, 5500),
(1, 5600),
(2, 1000),
(2, 1000);

select client, count(payment) from
(select *, avg(payment) over(partition by client) as avg_payment from Customers) as t1
where payment > 5000
group by client
having count(payment)>5

But I have to make it without window function and subquery. I've been told it is possible to do it only with the use of CASE function. I'll be happy if someone could help me optimize my query.

Notice that for your example table, the expected result is empty, since client 1 has exactly 5 payments exceeding 5000$, not more than 5 payments. — Bergi
– Bergi, Commented Oct 10, 2023 at 20:37

Bergi · Accepted Answer · 2023-10-10 21:03:10Z

1

You can get rid of the subquery by placing the aggregation directly in the having clause:

select client
from Customers
group by client
having count(*) filter(where payment > 5000) > 5
   and avg(payment) > 10000

^{(online demo)}

_{I prefer count(*) over count(payment) since the latter does not count rows with a NULL value, though it doesn't matter here due to the > 5000 condition.}

Now instead of using filter, you can use a sum that conditionally counts either 1 or 0 per row, and use a CASE statement for that:

…
having sum(case when payment > 5000 then 1 else 0 end) > 5

or

…
having count(case when payment > 5000 then 1 /* else null */ end) > 5

or

…
having sum((payment > 5000)::int) > 5

though using filter is much more elegant and straightforward. See also postgresql - sql - count of `true` values.

edited Oct 10, 2023 at 21:03

answered Oct 10, 2023 at 20:44

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Stickleback Over a year ago

Can’t suggest edit, should this be select count(distinct client)….?

Bergi Over a year ago

@Stickleback Not necessary with group by client

Stickleback Over a year ago

thanks. For my understanding will this return a client ‘id’ list vs count of client?

Bergi Over a year ago

@Stickleback Yes indeed. I don't how how to return a single count without a subquery or CTE

Bergi Over a year ago

@Stickleback I see now what you meant, but count(distinct client) does not work, as it still does group by client so it always returns 1.

|

Zack · Accepted Answer · 2023-10-10 20:48:28Z

TLDR: Working fiddle here

Let's break the query down into pieces:

Find customers who have more than 5 payments that exceed 5000 dollars

You can query for payments more then $5,000 in your WHERE clause, and then specify the "more than 5 payments" in your HAVING clause (after aggregating by Client ID):

SELECT 
  client, 
  COUNT(*) AS payment_gt_5000
FROM customers
WHERE payment > 5000
GROUP BY client
HAVING COUNT(*) >= 5

(note that I changed >5 to >=5, since Client ID 1 has exactly 5 matching payments).

Then if we wanted to capture "average payment value of more than 10,000 dollars", we'd use a very similar query:

SELECT 
  client, 
  AVG(payment)
FROM customers
GROUP BY client
HAVING AVG(payment) > 10000

Since these 2 queries are very similar, we should be able to combine them. The only tricky part is we have to get rid of the payment > 5000 from the WHERE clause, since we want to calculate averages for all payments. But wait…it's a bird! It's a plane! It's conditional aggregation to the rescue:

SELECT 
  client, 
  COUNT(CASE WHEN payment > 5000 THEN 1 END) AS payment_gt_5000,
  AVG(payment) AS avg_payment
FROM customers
GROUP BY client
HAVING
    COUNT(CASE WHEN payment > 5000 THEN 1 END) >= 5
    AND AVG(payment) > 10000

We're not applying the payment > 5000 to the WHERE clause, so we're getting the average for all payments like we want. But we're still getting the count of payments > 5000 (COUNT(CASE WHEN payment > 5000 THEN 1 END)), so we can still figure out in the HAVING clause which clients have 5+ payments of more than $5,000.

Stickleback · Accepted Answer · 2023-10-10 22:20:01Z

0

Technically, based on the question ‘Count the number of customers....’ there isn’t a way of doing this using a single select statement without a join.

It would require either a window function or CTE or subquery to return the aggregation. This is because you cannot run two different groupings within the same selection without a window function (which is required for the initial average and then to count the client IDs)

answered Oct 10, 2023 at 22:20

Stickleback

3352 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to write SQL query with multiple conditions using CASE function

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related