PostgreSQL, CASE WHEN and IF

Question

I have a table with some cities, restaurants and orders (per restaurant) for a specific year. From this dataset, I have a problem stating that:

In cities with average orders higher than 120, restaurants with less than 100 order should be aborted.
In cities with average orders higher than 60, restaurants with less than 40 order should be aborted.
In cities with average orders higher than 30, restaurants with less than 20 order should be aborted.

Until now, I have created a query that can give me a table with the average orders for each city and label them as 1, 2, 3 or 4 as follows:

SELECT cities, AVG(orders), COUNT(restaurants),
      CASE 
         WHEN (AVG(orders) >= 120) THEN '1'
         WHEN (120 > AVG(orders) >= 60) THEN '2'
         WHEN (60 > AVG(orders) >= 30) THEN '3'
         ELSE '4'
         END AS ranking
FROM c_cities
GROUP BY 1

So, now I want to create a statement that will check for example: for rating = 1, if orders > 100 -> keep them or label them as something. for rating = 2, if orders > 60 -> similar and etc.

I am trying to find the logic and the right statement in it. I think that a CASE ... WHEN cannot solve my problem. I am reading the documentation for IF statement, but I cannot make it work.

I would be grateful for any help you could provide :)

Please add some sample data and the expected output based on that data (formatted text please, no screenshots. Or even better: create table and insert statements) — user330315
– user330315, Commented Jan 6, 2016 at 20:11

Gordon Linoff · Accepted Answer · 2016-01-06 20:14:08Z

1

I follow the logic in your bullet points, but not the rest of the question. To implement that logic, use window functions and then where. For instance, the following gets the restaurants that should be "aborted" according to your definition:

select cr.*
from (select c.city, c.restaurant, avg(orders) as restaurant_avg,
             (sum(orders) over (partition by city) /
              count(distinct restaurant) over (order by city)
             ) as city_avg
      from c_cities
      group by c.city, c.restaurant
     ) cr
where (city_avg > 120 and restuarant_avg < 100) or
      (city_avg > 100 and restaurant_avg < 80) or
      (city_avg > 30 and restaurant_avg < 20);

The idea is to get the city and restaurant average calculated for each row. Then you can just compare them.

answered Jan 6, 2016 at 20:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Nasia Ntalla Over a year ago

cool :) Actually partition by is what I was looking for :) Thanks a lot!

Collectives™ on Stack Overflow

PostgreSQL, CASE WHEN and IF

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related