按付款类型列出的销售金额组

Question

下面是使用Cash&卡片现在有了一项新的要求来节省现金&CashAmount和CardAmount中的卡交易将被保存以识别不同的金额，如下所示。

Sales   Payment_type    CashAmount  CardAmount
9.00    Card                0.00    0.00
10.00   Cash                0.00    0.00
8.80    Cash_Card           5.00    3.80
9.35    Cash_Card           5.00    4.35

现在，在月度销售报表中，我们显示现金、卡、支票交易金额。现金&卡交易现金金额现在也应该添加到现金列，卡销售金额应该添加到卡列。最终输出

Sales  Payment_type    
17.15          Card                
20.00          Cash     

select 
sum(a.total_amount) as Sales,
b.Payment_type          
from
sl_sales_trans_master a
inner join sl_payment_master b on a.payment_type_id = b.payment_type_id            
where
a.reading_master_id=@ReadingMasterID
group
by b.Payment_type  

你能指导我如何处理这个问题吗。

谢谢。

Answer 1

您可以编写一个输出如下样式的查询：

| Sum of Card Payments | Sum of Cash Payments |
-----------------------------------------------
|                17.15 |                20.00 |

该查询如下所示：

SELECT
SUM(
CASE
WHEN t.Payment_Type = 'Cash_Card' THEN t.CardAmount
WHEN t.Payment_Type = 'Card' THEN t.Sales
ELSE 0.0
END
) AS "Sum of Card Payments",
SUM(
CASE
WHEN t.Payment_Type = 'Cash_Card' THEN t.CashAmount
WHEN t.Payment_Type = 'Cash' THEN t.Sales 
ELSE 0.0
END
) AS "Sum of Cash Payments"
FROM
sl_sales_trans_master t

Answer 2

另一种变体。您还没有发布表结构的精确细节，但希望这种方法相当清晰，可以根据需要进行调整。从本质上讲，它将Cash_Card组合视为两个独立的行。

with cte as (
select payment_type, sales as amount from sl_sales_trans_master where payment_type in ('Cash', 'Card')
union all
select 'Cash', cashamount from sl_sales_trans_master where payment_type = 'Cash_Card'
union all
select 'Card', cardamount from sl_sales_trans_master where payment_type = 'Cash_Card'
)
select sum(amount) as amount, payment_type from cte group by payment_type

Answer 3

基本上，您需要从sales列或适当的列中提取每种支付类型的数据。您可以使用带有一些条件聚合的横向连接来实现这一点：

select v.payment_type, sum(v.sales) as sales
from sl_sales_trans_master stm cross apply
(values ('Cash', (case when stm.payment_type = 'Cash' then stm.sales else stm.cashamount end),
('Card', (case when stm.payment_type = 'Card' then stm.sales else stm.cardamount end)
) v(payment_type, sales)
group by v.payment_type;

或者，您可以将值放在单独的列中：

select sum(case when stm.payment_type = 'Cash' then stm.sales else stm.cashamount end) as cash,
sum(case when stm.payment_type = 'Credit' then stm.sales else stm.creditamount end) as credit 
from sl_sales_trans_master stm

Answer 4

我创建了一个小提琴。虽然没有@deHaar的回答那么高效或简洁，但我发现这一回答很容易验证。

select type, sum(amount)
from (
select
sum(sales) as amount, 'total_card_only' as detail, 'card' as type
from sl_sales_trans_master 
where Payment_type = 'Card'
union 
select sum(cardAmount), 'total_card_mixed', 'card' as type
from sl_sales_trans_master 
where Payment_type = 'Cash_Card'
union
select
sum(sales), 'total_cash_only', 'cash' as type
from sl_sales_trans_master
where Payment_type = 'Cash'
union 
select sum(cashAmount), 'total_cash_mixed', 'cash' as type
from sl_sales_trans_master 
where Payment_type = 'Cash_Card') as temp
group by type