Nasia Ntalla Nasia Ntalla - 5 years ago 641
SQL Question

PostgreSQL, CASE WHEN and IF

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download