CODEWITHSUNDEEP
sqlsql-server
Mark
Mark

Reputation: 3

SQL two different Aggregate functions with LEFT Join

How can I return two aggregate function with different condition with a LEFT join?

I already have this: 

SELECT VehicleType.vehicleTypeName, COUNT(*) as SALE
FROM Transactions
LEFT JOIN VehicleType
ON Transactions.VehicleTypeID = VehicleType.vehicleTypeID
WHERE Transactions.isRefund = 0
GROUP BY VehicleType.vehicleTypeName

This returns the gross vehicle count 

Name      | Sale
---------------
vehicle1  | 10
vehicle2  | 15

I want to know how to get the net count per vehicle (Count of Vehicles as Sale less the count of vehicles as refund) if possible

Name      | NetCount
---------------
vehicle1  | 8
vehicle2  | 10

If not something like this.

Name      | Sale | Refund
-------------------------
vehicle1  | 10   | 2
vehicle2  | 15   | 5

Upvotes: 0

Views: 46

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6465

You can calculate it suming a conditional expression :

SELECT VehicleType.vehicleTypeName, 
       sum(case when Transactions.isRefund = 0 then 1 else 0 end) as Sale,
       sum(case when Transactions.isRefund = 1 then 1 else 0 end) as Refund
FROM Transactions
     LEFT JOIN VehicleType ON Transactions.VehicleTypeID = VehicleType.vehicleTypeID
GROUP BY VehicleType.vehicleTypeName

And your first result would be :

SELECT VehicleType.vehicleTypeName, 
       sum(case when Transactions.isRefund = 0 then 1 else -1 end) as NetCount
FROM Transactions
     LEFT JOIN VehicleType ON Transactions.VehicleTypeID = VehicleType.vehicleTypeID
GROUP BY VehicleType.vehicleTypeName

Upvotes: 1

Related Questions