如何获得一列的总和 [英] How to get the total sum for a column

问题描述

我有以下查询，该查询可为我提供所需的数据，但是，我需要CASE语句中Cash，Credit和Check列的总和.我怎样才能做到这一点?如果可能的话，我想使用一个过程.

I have the following query that gives me the data that I want, however, I need the total sum of the Cash, Credit and Check columns inside my CASE statements. How can I achieve this? I'd like to use a procedure for this if possible.

另外，对我来说，此查询似乎效率不高.任何人都可以对此进行改进吗?在我看来，我应该能够设置变量，然后在CASE语句中使用它们，但不确定如何完成.

Also, to me, this query doesn't seem at all that efficient. Can anyone improve upon this? It seems to me that I should be able to set variables and then use them inside my CASE statements but not sure how it's done.

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

当我说我需要现金，贷方和支票列的总和"时，是指每列各自的总计.

When I say that I "need the total sum of the Cash, Credit and Check columns", I mean the respective totals per column.

推荐答案

这是一个令人毛骨悚然的查询，但是对已经拥有的内容进行简单的扩展后，就会得出我认为您要的内容:

That is a gruesome query, but a simple-minded extension to what you already have gives you what I think you are asking for:

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

IN(...)表示法可能无效；如果不是，则可以写出三向OR条件.但是，我忍不住认为必须有一种更好的查询结构.

The IN(...) notation might not work; if not, you can write out a three-way OR condition instead. However, I can't help but think there has to be a better way of structuring your query.

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

This query gives you the basic details, including the payment type. Save it into a temporary table, or use a named subexpression in a WITH clause if your DBMS supports that.

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

然后您可以分别或联合汇总卡，支票和现金.

You can then aggregate the cards, the checks and the cash separately and jointly.

这篇关于如何获得一列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！