How to subtract from another table in SQL-CodePudding

SELECT 
COUNT(ca.Plate) as 'OccupiedElectricSlots'
FROM cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on  PS.Id = Ca.ParkingSlotsId
WHERE ps.ElectricOutlet = 1
GROUP BY hs.HouseName, C.CityName

SELECT 
 MAX(Ps.SlotNumber) as 'ParkingSlotTotal'
,MAX(PS.SlotNumber) - Count(ca.Plate) as 'FreeSlots'
,SUM(CAST(PS.ElectricOutlet AS INT)) as 'ElectricOutlet'
,Hs.HouseName
,C.CityName
FROM Cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on  PS.Id = Ca.ParkingSlotsId
GROUP BY hs.HouseName, C.CityName

How can I subtract the first tables numbers on the second one? I want to see how many free slots that have electric outlet. Like this Column ElectricOutlet - OccupiedElectricSlots = result

I'm quite new at SQL, but I have tried to outer apply (don't fully understand it), and I tried to join them both tables togheter. Tried different where conditions but I'm stuck atm.

CodePudding user response：

Your queries are almost identical as far as I can see. You can change your first query to:

SELECT COUNT(CASE WHEN ps.ElectricOutlet = 1 THEN ca.Plate END) as 'OccupiedElectricSlots'
FROM cities C
JOIN ParkingHouses HS on C.Id = hs.CityId
JOIN ParkingSlots PS on HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca on  PS.Id = Ca.ParkingSlotsId
GROUP BY hs.HouseName, C.CityName

I.e., instead of filtering on ps.ElectricOutlet you just ignore those rows in COUNT. Now you can just:

SELECT 
[...]
,SUM(CAST(PS.ElectricOutlet AS INT)) - COUNT(CASE WHEN ...) AS result  
[...]
FROM Cities C
JOIN ParkingHouses HS 
    ON C.Id = hs.CityId
JOIN ParkingSlots PS 
    ON HS.Id = ps.ParkingHouseId
LEFT JOIN Cars Ca 
    ON PS.Id = Ca.ParkingSlotsId
GROUP BY hs.HouseName, C.CityName

CodePudding user response：

The MINUS operator is used to subtract the result set obtained by first SELECT query from the result set obtained by second SELECT query. MINUS compares the data in two tables and returns only the rows of data using the specified columns that exist in the first table but not the second.