PressingOnAlways PressingOnAlways - 4 months ago 16
MySQL Question

MySQL - Counting Groups That Meet Certain Criteria

I have the following tables:

Family




  • ID INT AUTO_INCREMENT

  • CountryOfResidence STRING



Person




  • ID INT AUTO_INCREMENT

  • FamilyID INT FOREIGN KEY

  • DOB DATE



I am trying to find the count of all the families by CountryOfResidence that contain somebody born before 1970.

I can't simply do a JOIN with
Person
and
Family
as that would give me the unique count of every person. I want the family to be counted only once when any person within the family is born before 1970.

Can someone help me craft this query? I feel like a subquery would accomplish what I am looking for, but I am afraid about the performance.

EDIT

Added SQL Fiddle: http://sqlfiddle.com/#!9/b92e3/1/0

Answer

You can use EXISTS for this:

SELECT CountryOfResidence , COUNT(*)
FROM Family AS f
WHERE EXISTS (SELECT 1
              FROM Person AS p
              WHERE f.ID = p.FamilyID AND  Year(DOB) < 1970) 
GROUP BY CountryOfResidence 
Comments