sql - 查找销售百分比和退货百分比

Question

我在 MS SQL DB 中有一个表，其中包含任何公司的交易，有两列

1. sub_cat_code
2. total_amount

有时包含正值，total_amount有时包含负值。其中正值表示销售，负值表示退货。

现在，我需要使用以下查询来查找销售百分比和退货百分比，以返回列的百分比表示，每个值都相同。任何帮助都将是可观的。

Select prod_subcat_code , 
(sum(total_amt)*100 /(select sum(total_amt) from transection)) as 
Sale_pers,
((select sum(total_amt) from transection where total_amt<0)*100/(select 
 sum(total_amt) from transection)) as return_sale
 from transection
 group by prod_subcat_code
 order by Sale_pers desc

标签： sqlsql-server

Answer 1

使用返回表的总销售额和总回报的 CTE：

with cte as (
  select 
    sum(case when total_amount > 0 then total_amount else 0 end) total_sales,
    sum(case when total_amount < 0 then total_amount else 0 end) total_returns
  from transection
)
select prod_subcat_code , 
  100.0 * sum(case when total_amount > 0 then total_amount else 0 end) / (select total_sales from cte) as sale_perc,
  100.0 * sum(case when total_amount < 0 then total_amount else 0 end) / (select total_returns from cte) as return_perc
from transection
group by prod_subcat_code

请参阅演示。