CODEWITHSUNDEEP
databasesqlite
Alpha93
Alpha93

Reputation: 61

Counting occurrences and selecting multiple columns

I have 2 tables chef and restaurant:

https://i.sstatic.net/Yo3fP.jpg

https://i.sstatic.net/zALXh.jpg

Id like to print print out in each location, how many restaurants did each chef find?

I want the result to SELECT the Location, chef.Name and Restaurant_Founded then print the total number of chefs that found a restaurant in that particular place so for example, in Leeds, 2 chefs found KFC. In Liverpool, only 1 chef found McDonalds. In Scotland, 2 chefs found Subway and finally, in Burnley, 1 chef found Dominos.

I have tried using the COUNT (restaurant_founded) then grouping it by chef.Name and Location and ofc INNER JOINING restaurant but that doesn't seem to work and Im unsure how to chnage it or what to add to display the information correctly. Im pretty sure those are the queries i need to use. Im not sure if I need to use something else though?

Upvotes: 0

Views: 43

Answers (2)

Varun Verma
Varun Verma

Reputation: 542

Basically you have to join the 2 tables and count.

The following query gives you the desired result : 

select r.location, c.rest_found, count(*) from restaurants as r 
inner join chef_data as c on r.name = c.rest_found group by location, rest_found

Output:

"Burnley","Dominoes","1"
"Leeds","KFC","2"
"Liverpool","McDonalds","1"
"Liverpool","Starbucks","1"
"Scotland","Subway","2"

Upvotes: 1

kuro
kuro

Reputation: 3236

If I understood your problem correctly, I guess joining the two table on the basis of Restaurant name and selecting count of Restaurant found along with other columns you need and then grouping it by only Restaurant name will do the job.

Upvotes: 1

Related Questions