How to count the number of orders and the number of returns from same order id?

I’m trying to derive a table as CustomerID, OrderCount and ReturnCount from a table with columns as CustomerID, OrderID, ReturnFlag

I’ve tried the following code that gives me 2 distinct tables but I want the results in a single output table.

select "CustomerID", count(DISTINCT "OrderID") as OrderCount  
from "Customer" 
group by "CustomerID";

select "CustomerID", count(DISTINCT "OrderID") as ReturnCount 
from "Customer" 
where "ReturnFlag" = 1 
group by "CustomerID";

Source Table –

|CustomerID| |OrderID| |ReturnFlag|
|1     | |  A    | |    0 |
|2     | |   B   | |        1 |
|1     | |  C    | |    0 |
|2     | |   D   | |    0 |
|3     | |   E   | |    1 |

Expected output table –

|CustomerID| |OrderCount| |ReturnCount|
|1     | |  2      | |  0     |
|2     | |  2      | |        1  |
|3     | |  1      | |  1     |

Answer

Use a case expression to do conditional aggregation:

select "CustomerID",
       count(DISTINCT "OrderID") as OrderCount,
       count(DISTINCT case when "ReturnFlag" = 1 then "OrderID" end) as ReturnCount
from "Customer"
group by "CustomerID";

Advertisement

Answer