user2475110 user2475110 - 2 months ago 9
MySQL Question

SQL/mySQL - Average of Differences

I'm new to MySQL and I'm trying to find the average span of first vs last birth of each family of 4 children in mySQL.
I have the following tables :


  • family (id,last_name)

  • lookup(family_id,child_id)

  • children(id,first_name,year_of_birth)



I've used the following query but just wondered if anybody can suggest something simpler(without using views) in mySQL - if that's even possible? Maybe some formulas/tricks I haven't thought of ?

SELECT
AVG(span)
FROM (SELECT
family_id,
MAX(year_of_birth) - MIN(year_of_birth) span
FROM (family
INNER JOIN lookup
ON family.id = lookup.family_id)
INNER JOIN children
ON children.id = lookup.child_id
WHERE family_id IN (SELECT
family_id
FROM (family
INNER JOIN lookup
ON family.id = lookup.family_id)
INNER JOIN children
ON children.id = lookup.child_id
GROUP BY family_id
HAVING COUNT(first_name) = 4)
GROUP BY family_id) AS derivedTable;


Thanks !

pid pid
Answer

Based on interpretation of the attribute names, this should be enough. Test it before you use it in production, I'm actually guessing what the names mean.

SELECT AVG(*)
FROM (
    SELECT MAX(year_of_birth) - MIN(year_of_birth)
    FROM lookup INNER JOIN children ON child_id = id
    GROUP BY family_id
    HAVING COUNT(*) = 4);