Working 6

1 files changed, 14 insertions, 1 deletions

diff --git a/db_2017_cw1.sql b/db_2017_cw1.sql
index 1f564b6..3523f37 100644
--- a/db_2017_cw1.sql
+++ b/db_2017_cw1.sql
@@ -46,7 +46,7 @@ ORDER BY popularity DESC, p.name
 ;
 
 -- Q6 returns (name,forties,fifties,sixties)
-SELECT father
+SELECT father AS name,
        COUNT(CASE WHEN dob >= '19400101' AND dob < '19500101'
                   THEN name ELSE NULL END) AS forties,
        COUNT(CASE WHEN dob >= '19500101' AND dob < '19600101'
@@ -55,6 +55,19 @@ SELECT father
                   THEN name ELSE NULL END) AS sixties
 FROM person
 GROUP BY father
+HAVING COUNT(father) >= 2
+UNION
+SELECT mother AS name,
+       COUNT(CASE WHEN dob >= '19400101' AND dob < '19500101'
+                  THEN name ELSE NULL END) AS forties,
+       COUNT(CASE WHEN dob >= '19500101' AND dob < '19600101'
+                  THEN name ELSE NULL END) AS fifties,
+       COUNT(CASE WHEN dob >= '19600101' AND dob < '19700101'
+                  THEN name ELSE NULL END) AS sixties
+FROM person
+GROUP BY mother
+HAVING COUNT(mother) >= 2
+ORDER BY name
 ;