如何在SQL中求出产品明智的总和

Question

我有两张桌子product_master和party_master。

表party_master有以下列：

Total life sum assured
Term Insurance
Mediclaim
Top Up
P.A Policy
Traditional Insurance
Vehical Insurance
Fixed Deposit
SIP

每一列都有数值。我想编写一个查询，将所有列值与产品类别相加。我怎样才能得到这个查询呢？

select 
    pk_pid, name, 
    sum() as portfolio_am 
from 
    dbo.product_master 
order by 
    name

如何做到这一点？请帮帮我..。

更新**

屏幕截图

​

​

Answer 1

您可以按以下方式使用group by和join：

select product.pk_pid , product.name, 
       sum("Total life sum assured" + "Term Insurance Mediclaim" 
           + ... + SIP) as portfolio_am 
  from dbo.product_master product
  join dbo.party_master party on product.pkid = party.fk_column_name
 group by product.pk_pid , product.name
 order by product.name

--更新

因此，所有产品都有product_master表，每个产品都有一个列的party_master表。

您可以按以下方式使用条件聚合：

select product.pk_pid , product.name, 
       sum(case when product.name = 'Total life sum assured' then "Total life sum assured"        
                when product.name = 'Term Insurance Mediclaim' then "Term Insurance Mediclaim"        
                .......
                .......
                when product.name = 'SIP' then SIP 
           end) as portfolio_am 
  from dbo.product_master product
  join dbo.party_master party on product.pkid = party.fk_column_name
 group by product.pk_pid , product.name
 order by product.name

总人寿保险定期保险医疗保险自上而下一种传统的保险保单固定存款保险