Question 114314 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I find a president whose wife is older than 30 and occured only once (no duplication in pres_id)

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search