sql - PostgreSQL 中 INNER JOIN 中字段的 SUM

Question

我有一个这样的查询:

SELECT      pt.id, 
            pt.plan_name,
            pt.product_disc_amt,
            pt.product_disc_perc, 
            SUM(ac.premium_amount) AS premium,
            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, 
            ac.premium_amount,
            ac.id

在此查询中，age_classification 是与产品的 One2Many 关系。它通过p_id关联。

我得到这样的o/p:

id      plan_name   product_disc_amt    product_disc_perc       premium         ac_id
295     A           0                   0                       110             75
1543    B           0                   0                       90              69
1543    B           0                   0                       95              70
1543    B           0                   0                       132             71

我的预期输出是:

id      plan_name   product_disc_amt    product_disc_perc       premium         ac_id
295     A           0                   0                       110             75
1543    B           0                   0                       317             69 <------ Sample Value

ac_id 不必是 69。我只是在那里写了一个 id，它位于我得到的输出中。 ac_id 中不需要有值。我只是给出了专栏来展示这种关系。由于它是在 SELECT 语句中给出的，因此我不知道预期输出中会出现什么值。所以我给出了一个值。

也就是说，我想获取 id 1543 上的 premium_amount 总和。该行正在重复。由于我是 SQL 新手，因此我没有找到解决方法。提前致谢。

Answer 1

您还应该将 plan_name 纳入 GROUP BY。 ac_id 列还可以防止行合并。在这里，聚合可以提供帮助:

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