如何获取某一列的总和

我有以下查询，它为我提供了所需的数据，但是，我需要 CASE 语句中的现金、信用和支票列的总和。我怎样才能实现这个目标？如果可能的话，我想为此使用一个程序。

另外，对我来说，这个查询似乎并不那么有效。有人可以对此进行改进吗？在我看来，我应该能够设置变量，然后在我的 CASE 语句中使用它们，但不确定它是如何完成的。

SELECT
t1.order_id,
t3.price * SUM( t1.quantity ) AS subtotal,
( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
CASE t2.payment_type WHEN 'Cash'  THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS cash,
CASE t2.payment_type WHEN 'Card'  THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS card,
CASE t2.payment_type WHEN 'Check' THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS check
FROM pos_item_order AS t1
Join pos_order AS t2 ON t2.order_id = t1.order_id
Join inv_item AS t3 ON t3.item_id = t1.item_id
GROUP BY t1.order_id, t1.item_id

编辑：当我说我“需要现金、信用和支票列的总和”时，我指的是每列各自的总计。

这是一个可怕的查询，但是对您已有的内容进行简单的扩展可以满足我认为您所要求的：

SELECT t1.order_id,
       t3.price * SUM( t1.quantity ) AS subtotal,
       ( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
       t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
       t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
       CASE t2.payment_type WHEN 'Cash'
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
       ELSE 0.00 END AS cash,
       CASE t2.payment_type WHEN 'Card'
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
       ELSE 0.00 END AS card,
       CASE t2.payment_type WHEN 'Check'
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100
       ELSE 0.00 END AS check,
       CASE WHEN t2.payment_type IN ('Cash', 'Card', 'Check')
       THEN t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 ELSE 0.00 END AS cash_card_check
  FROM pos_item_order AS t1
  JOIN pos_order AS t2 ON t2.order_id = t1.order_id
  JOIN inv_item AS t3 ON t3.item_id = t1.item_id
 GROUP BY t1.order_id, t1.item_i

The IN(...)符号可能不起作用；如果没有，你可以写一个三路OR条件代替。但是，我忍不住认为必须有更好的方法来构建查询。

此查询为您提供基本详细信息，包括付款类型。将其保存到临时表中，或者在WITH子句中使用命名子表达式（如果您的DBMS支持的话）。

SELECT t1.order_id,
       t2.payment_type,
       t3.price * SUM( t1.quantity ) AS subtotal,
       ( SUM( t1.discount ) + t3.discount ) / 100 AS disc,
       t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS tax,
       t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS total,
       t3.price * SUM( t1.quantity ) - ( SUM( t1.discount ) + t3.discount ) / 100 + t3.price * SUM( t1.quantity ) * ( t3.tax_state + t3.tax_fed ) /100 AS payment
  FROM pos_item_order AS t1
  JOIN pos_order      AS t2 ON t2.order_id = t1.order_id
  JOIN inv_item       AS t3 ON t3.item_id = t1.item_id
 GROUP BY t1.order_id, t1.item_i, t2.payment_type

然后，您可以分别或合并汇总卡、支票和现金。