CODEWITHSUNDEEP
sqljoin
CodingStark
CodingStark

Reputation: 209

How group by count from multiple tables

I have 3 different tables, country, city, and customer. Tables are shown below:

country table:

id   country_name
1    UK
2    US
3   Brazil
:    
n   Canada

city table

id  city_name  postal_code  country_id
1    London        30090         1
2    Dallas        20909         2
3    Rio           29090         3
4    Atlanta       30318         2
:
n    Vancouver     32230         n

customer table

id    customer_name     city_id
1        John             1
2        Pete             3
3        Dave             2
4        May              2
5        Chuck            4
6        Sam              3
7        Henry            3

***country.id is references city.country_id, and city.id is references customer.city_id

I want to write a query that can extract the country name, city name and the count of the customer of the associate city. But with one condition, the query will return all cities with more customers than the average number of customers of all cities

It will look something like below, this is the correct output

UK London 2
Brazil Rio 3

but I kept getting this output, which isn't correct

UK London 2
US Dallas 2
US Atlanta 1
Brazil Rio 3

I fixed my SQL query but it doesn't give me the result that I want

SELECT country.country_name, city.city_name, COUNT(customer.city_id) from country 
JOIN city on country.id = city.country_id 
JOIN customer on city.id = customer.city_id
Group by city_name,country.country_name;

I am wondering how can I do this and fix my code?

Upvotes: 0

Views: 502

Answers (2)

Atif
Atif

Reputation: 2210

You are missing country.country_name in the query it will give you error. As a general rule all columns on which aggregate function is not applied should be part of group by clause.

So either you write your query without country_name in the select or add country_name in the group by clause.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

add country.country_name in group by

SELECT country.country_name, city.city_name, COUNT(customer.city_id) from country 
JOIN city on country.id = city.country_id 
JOIN customer on city.id = customer.city_id
Group by city_name,country.country_name

Upvotes: 1

Related Questions