sql-server - 如何在 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

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

更新**

截屏

标签： sql-servertsql

Answer 1

您可以使用group byandjoin如下：

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

总保额 定期保险 Mediclaim 充值 PA 保单 传统保险 车辆保险 定期存款 SIP