Sql: Count More Than Average Having Vs. Where

Your current query is one valid way to solve this problem, but maybe the recipient of your answer was expecting a different solution. A more modern way of doing this might involve analytic functions:

WITH cte AS (
    SELECT co.country_name, COUNT(customer_name) cnt,
           AVG(COUNT(customer_name)) OVER () avg_cnt
    FROM country co
    INNER JOIN city ci ON co.id = ci.country_id
    INNER JOIN customer cu ON ci.id = cu.city_id
    GROUP BY co.country_name
)

SELECT country_name, cnt
FROM cte
WHERE cnt > avg_cnt;

In the CTE above, as we turn out the counts for each country name, we also compute the average count across your entire original result set. Then, all we have to is subquery and compare each count against the average.

I tried below query and it worked for me.

WITH CTE AS(
SELECT DISTINCT CO.country_name, Ci.city_name,COUNT(city_id) AS cnt
, AVG(COUNT(city_id)) OVER() avg_cnt
FROM COUNTRY CO
INNER JOIN City CI ON CI.country_id = CO.id
INNER JOIN Customer CU ON CU.city_id = CI.id
GROUP BY CO.country_name,CI.city_name
)

SELECT country_name,city_name, cnt
FROM CTE WHERE cnt > avg_cnt ORDER BY country_name

Let me know if this works for you. Thanks

The logic is same, fetching queries differ.

Write a query which will return all city name, country name and number of customers with more customers than the average number of customers of all cities in ascending order.

SELECT city.city_name, country.country_name, COUNT(customer.id) AS 
num_customer
FROM city
JOIN country ON city.country_id = country.id
JOIN customer ON city.id = customer.city_id
GROUP BY city.id
HAVING num_customer > (SELECT AVG(num_customer) FROM (SELECT city.id, 
COUNT(customer.id) AS num_customer
FROM city
JOIN country ON city.country_id = country.id
JOIN customer ON city.id = customer.city_id
GROUP BY city.id) AS temp)
ORDER BY country.country_name;

I just added ci.city_name in select and group by clauses of the main query and it worked for me.

SELECT co.country_name,ci.city_name COUNT(customer_name)
FROM
    country co
        JOIN
    city ci ON co.id = ci.country_id
        JOIN
    customer cu ON ci.id = cu.city_id
GROUP BY co.country_name , ci.city_name
HAVING COUNT(customer_name) > (SELECT 
        AVG(temp.cnt)
    FROM
        (SELECT 
            ci.id, COUNT(customer_name) AS cnt
        FROM
            city ci
        JOIN customer cu ON ci.id = cu.city_id
        GROUP BY ci.id) temp)
ORDER BY co.country_name ASC;

with cty as(SELECT city.id, country.country_name,city.city_name
COUNT(customer.id) AS num_customer
FROM city
JOIN country ON city.country_id = country.id
JOIN customer ON city.id = customer.city_id
GROUP BY city.id 
order by country.country_name)
SELECT  country.country_name,city.city_name, num_customer 
from cty where num_customer>(select avg(num_customer) from cty );

with custcity as
(
select
    city_id,
    count(*) as customers_per_city
from customer
group by city_id
),
avg_cust_city as
(
    select
        avg(customers_per_city) as avg_cust_per_city
    from custcity
),
num_cust as
(
select
    cus.city_id,
    count(cus.customer_name) as num_customers
from
    customer cus
    left join
    city cty
        on cus.city_id = cty.id
    left join
    country cnt
        on cnt.id = cty.country_id
group by cus.city_id
having num_customers > (select avg_cust_per_city from avg_cust_city)
)
select
    cnt.country_name,
    cty.city_name,
    nc.num_customers
from
    customer cus join num_cust nc
    on  cus.city_id = nc.city_id
    left join city cty
        on cus.city_id = cty.id
    left join country cnt
        on cnt.id = cty.country_id
order by
    country_name
;