如何在SQL中相互减去两列（PostgresSQL）

I have an orders table with 4 columns. There are three important columns -

我有一个4列的订单表。有三个重要的专栏 -

dateordered orderstatus - either "completed" or "returned" orders - the number of orders put in

datetodered orderstatus - “已完成”或“已退回”订单 - 放入的订单数量

I want to find the sum of each month's completed orders and subtract the returned orders for a remaining total per month. This is my code so far:

我想找到每个月已完成订单的总和，并减去每月剩余总额的退货订单。到目前为止这是我的代码：

SELECT date_trunc('month', dateordered), SUM(orders) - (
    SELECT SUM(orders)
    FROM c_orders
    WHERE orderstatus = 'returned'
)
FROM c_orders
WHERE orderstatus = 'complete'
GROUP BY 1

I know the main body of the query works correctly to pull the completed orders by month. The problem is the subquery - I don't know how to specify the sum of returned orders by month. So right now this query is subtracting the sum of all returned orders throughout the entire dataset without specifying month.

我知道查询的主体正常工作以按月拉出已完成的订单。问题是子查询 - 我不知道如何按月指定返回订单的总和。所以现在这个查询在不指定月份的情况下减去整个数据集中所有返回订单的总和。

Is there a better way to do this? TIA

有一个更好的方法吗？ TIA

1 个解决方案

#1

I think this is what you want:

我想这就是你想要的：

SELECT date_trunc('month', dateordered) as yyyymm,
       SUM(CASE WHEN orderstatus = 'complete' THEN orders
                WHEN orderstatus = 'returned' THEN - orders
                ELSE 0
           END) 
FROM c_orders
GROUP BY yyyymm
ORDER BY yyyymm;