SUM of a field in INNER JOIN in PostgreSQL
You should also take plan_name
into GROUP BY
. The column ac_id
also prevents the rows from being combined. Here an aggregate could help:
SELECT pt.id,
pt.plan_name,
pt.product_disc_amt,
pt.product_disc_perc,
SUM(ac.premium_amount) AS premium,
MAX(ac.id) AS ac_id
FROM product pt
INNER JOIN age_classification ac
ON ac.p_id = pt.id
WHERE pt.medical_coverage_for = 'dependents_only'
OR (ac.relationship = 'child' AND ac.age_from::int <= 3 AND ac.age_to:: int >= 3
AND ac.gender = 'female' AND ac.marital_status = 'single')
OR (ac.relationship = 'child' AND ac.age_from::int <= 4 AND ac.age_to:: int >= 4
AND ac.gender = 'male' AND ac.marital_status = 'single' )
OR (ac.relationship = 'child' AND ac.age_from::int <= 2
AND ac.age_to:: int >= 2 AND ac.gender = 'female' AND ac.marital_status = 'single')
GROUP BY pt.id,
pt.plan_name,
ac.premium_amount,
pt.product_disc_perc