sql - 多列计数和聚合

Question

我有下表，其中有多个订单条目，订单可以被拒绝或批准。

---

Amount  | Approved | Rejected | OrderNo 
-------------------------------------------
 10     |   N      |    Y     |   10
 20     |   Y      |    N     |   10
 30     |   N      |    N     |   10
 40     |   Y      |    N     |   10
 22     |   N      |    Y     |   11
 10     |   N      |    N     |   10
--------------------------------------------

想建立一个可以总结的结果集。

OrderNo | TotalEntries | Approved_Or_Rejected_Entries | TotalAmount
-----------------------------------------------------------------
  10    |   5          |           3                  |    110
  11    |   1          |           1                  |     22

标签： sqlsql-servertsqlgroup-bypivot

Answer 1

使用条件聚合：

select
    orderno,
    count(*) totalentries
    sum(case when 'Y' in (approved, rejected) then 1 else 0 end) approved_or_rejected
    sum(amount) total_amount
from mytable
group by orderno