SQL-一些行的总和，减去其他行的总和 [英] SQL - sum of some rows, minus sum of other rows

问题描述

我在mySQL中有一个带有以下列的表:

I have a table in mySQL with the following columns:

CUSTOMER_CODE | TRANS_TYPE | TRANS_VALUE

TRANS_TYPE可以是"DRINV"(销售)或"DRCDT"(信用).

TRANS_TYPE could be either "DRINV" (a sale) or "DRCDT" (a credit).

我想获取每个客户的总销售额，所以到目前为止，我的查询是:

I want to get the total sales per customer, so my query so far is:

SELECT CUSTOMER_CODE, SUM(TRANS_VALUE) as SALES FROM DR_TRANS GROUP BY CUSTOMER_CODE

问题是，这是销售和积分的总和，而不是销售减去积分.我希望结果是

Problem is this is totaling the sales and credits, instead of giving be sales minus credits. I want the results to be

SUM(TRANS_VALUE) where TRANS_TYPE = "DRINV" - SUM(TRANS_VALUE) where TRANS_TYPE = "DRCDT".

是否可以在SQL查询中执行此操作?

Is it possible to do this in a SQL query?

推荐答案

select customer_code,
       sum(case
             when trans_type = 'DRINV' then
              trans_value
             else
              -trans_value
           end) as net_sales
  from dr_trans
 group by customer_code

这篇关于SQL-一些行的总和，减去其他行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！