How to select maximum discount for each family?

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:

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

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

Advertisement

Answer