I am trying to write a query to print the maximum number of discounted tours any one family can choose from. For example, the Thomas family can choose from any of the 3 tours and qualify for the discount. The Chris family only qualifies for 1.
Below is the Country table:
x
Country min_size
Bolivia 2
Brazil 4
China 9
Below is the Family table:
Name fam_size
Thomas 9
Chris 2
Here is my code but I think it’s incorrect even though it runs okay. Thank you in advance.
select name, count(countries.*) as cnt
from countries, families
where family_size <= min_size
group by 1
Advertisement
Answer
You can use inner join (to make the query more readable) and name in group by clause.
select name, count(country) as cnt
from families f inner join countries c
on f.family_size >= c.min_size
group by name