How can I find a president whose wife is older than 30 and occured only once (no duplication in pres-CodePudding

original question: Determine, for all presidents who married only woman older than 30, id and the sum of children number in all their marriages?

issue: The issue is that the pres_id 44 has three columns with 3 different presidents and their wives some were below and above 30, how can I only show a pres_id that does not contain a duplicate value (pres_id) and the spouse_age should always be bigger than 30?

SELECT pres_id, SUM(nr_children) as sumChild
FROM pres_marriage pm
WHERE spouse_age > 30
GROUP BY pres_id 
HAVING SUM(nr_children) >= 1
ORDER BY pres_id

current result:

pres_id   sumChild
23           3
32           1
42           2 
44           1

expected result:

pres_id sumchild
23         3
32         1
42         2

CodePudding user response：

You can exclude the results which doesn`t suit well. Exclusions can be made by joining the same table with another criteria. What i will do in your case. 1st Build another table:

SELECT pres_id, SUM(spouse_age) as youngSpouse
FROM pres_marriage pm
WHERE spouse_age < 31
GROUP BY pres_id

2nd Join the two tables, and add another where clause:

SELECT pres_id, SUM(nr_children) as sumChild
FROM pres_marriage pm
LEFT JOIN ( SELECT pres_id as pres_id2 , SUM(spouse_age) as youngSpouse
            FROM pres_marriage pm
            WHERE spouse_age < 31
            GROUP BY pres_id ) ON pres_id = pres_id2 
WHERE spouse_age > 30 AND youngSpouse is NULL
GROUP BY pres_id 
HAVING SUM(nr_children) >= 1
ORDER BY pres_id

CodePudding user response：

If you want only presidents who married only spouses older that 30, that means the minimum age of spouse for a particular president is more than 30.

So you can put it in the existing “having” clause. This should work for you:

SELECT 
    pres_id, SUM(nr_children) as sumChild
FROM 
    pres_marriage pm
GROUP BY 
    pres_id 
HAVING 
    SUM(nr_children) >= 1 
  AND 
    min(spouse_age) > 30
ORDER BY 
    pres_id