CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bypivot
Suresh
Suresh

Reputation: 25

Multiple column count and aggregation

I have following table which has multiple entries for an order and the order can either be rejected or approved. 

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
--------------------------------------------

Want to build a result set which can summarise.

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

Upvotes: 0

Views: 52

Answers (1)

GMB
GMB

Reputation: 222722

Use conditional aggregation:

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

Upvotes: 4

Related Questions