[SQL] Advanced count of more tables

1

I know it doesn’t say SQL in the forum overview but I guessed that some of you might be able to help :slight_smile:

Well, I’ve been fumbling around with this for quite some time and can’t seem to get the correct syntax (Access database, btw).

I have 2 tables of which only the following is important:

Areas: AreaId (the unique number)
Units: UnitId (unique), PlayerId, AreaId

Basically there can be a number of units (or none) in an area, represented by the AreaId of the Units table.

The resulting list should consist of:

  • AreaId (the total list from Areas)
  • TotalCount (for each AreaId the amount of UnitIds)
  • PlayerCount (for each AreaId the amount of UnitIds where PlayerId=another variable)

So it’s a list of all AreaId and for each AreaId you can see how many units are on this AreaId, and how many units which have the chosen PlayerId (and the counts could be 0). I hope this makes sense :slight_smile:

An example:

Units:
UnitId - PlayerId - AreaId
1 - 1 - 1
2 - 1 - 2
3 - 2 - 2
4 - 2 - 4

The result should be, if PlayerCount = 1:
AreaId - TotalCount - PlayerCount
1 - 1 - 1
2 - 2 - 1
3 - 0 - 0
4 - 1 - 0

Or is there a better way to do it?

Any help will be much appreciated :slight_smile: