SQL Join with SUM and Group By-CodePudding

I have an application in MVC5 C#. I want to add a feature where the admin can see how many parking spaces each parking lot has, along with how many spots are currently taken vs available.

Let's say, I have two tables (Lots and Staff).

the 'Lots' Table is the name of the existing parking lots and a count of how many spaces each lot has:

Name | Count
A    |  200
B    |  450
C    |  375

The 'Staff' Table contains each person's ID, and an int column for each lot. These columns are either 1 (employee is assigned to this lot) or 0 (employee not assigned to this lot

StaffID    |    LotA    |    Lot B    |    Lot C
7264       |      0     |       1     |      0
2266       |      0     |       0     |      1
3344       |      1     |       0     |      0
4444       |      0     |       1     |      0

In the above scenario, the desired output would be . . . .

 Lot |   Total |   Used   |  Vacant
  A  |     200 |    1     |   199
  B  |     450 |    2     |   448
  C  |     375 |    1     |   374

I have only done simple joins, in the past, and I am struggling to figure out whether it is best to use COUNT with a Where column = 1, or a Sum. But I also choke on the group by and get lost.

I have tried similar variations of the following (with no success)

SELECT Lots.Name,
       Lots.Count,
       SUM(Staff.LotA) As A_Occupied,
       SUM(Staff.LotB) as B_Occupied,
       SUM(Staff.LotC) as C_Occupied
FROM Lots
     CROSS JOIN Staff
GROUP BY Lots.Name

In theory, I expected this to yield a listing of the lots, how many spaces in each lot, and how many occupied in each lot, grouped by the lot name.

This generates an error in the Group By because I am selecting columns that are not included in the Group by, but that just confuses me even more as to how to Group By for my needs.

In general, I have a bad habit of trying to over complicate things and so I apologize ahead of time. I am not a 'classically trained coder'. I did my best to organize the question and make it understandable

CodePudding user response：

Firstly, your design would work better if it was properly normalised - your Staff table should have a single column representing the Lot that's used by each staff member (if you had 100 lots, would you have 100 columns?). Then there would be no need to pivot the data from columns into the rows you need.

With your current schema, you could use a cross apply to pivot the columns into rows which can then be correlated to each Lot name:

select l.[name] as Lot, q.Qty as Used, l.count - q.Qty as Vacant
from lots l
cross apply (
  select Sum(LotA) A, Sum(LotB) B, Sum(LotC) C
  from Staff s 
)s
cross apply (
  select Qty from (
    select * from (values ('A', s.A),('B', s.B),('C', s.C))v(Lot, Qty)
  )x
  where x.Lot = l.[name]
)q;

See Demo Fiddle

CodePudding user response：

is a layman solution. Hope it helps.

        with parking  as (
            select 'A' as lot ,sum(case when lota >0 then 1 else 0 end ) as lots from staff
            union all
            select 'B' as lotb , sum(case when lotb >0 then 1 else 0 end ) as lots from staff
            union all
            select 'C' as lotc, sum(case when lotc >0 then 1 else 0 end ) as lots from staff
            )
            select a.name, a.count, b.lots, a.count-b.lots as places
            from lots a  join parking b on a.name=b.lot