I have a SQL query where I’m calculating the number of players for a game based on `UserType`

(only `1`

counts as an actual player for the purposes of my calculation), and listing this info out for all games on a certain date:

```
SELECT
gm.[Id]
,SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) AS NbrPlayers
FROM
dbo.[Game] gm
LEFT JOIN dbo.[GameMembership] mbr ON gm.[Id] = mbr.[GameId]
WHERE
CAST(gm.[DateCreated] AS DATE) = CAST('2023-04-30' AS DATE)
GROUP BY
gm.[Id]
```

This gives the result, for my sample data:

```
Id NbrPlayers
==================================================
FFA8C1D6-CEB6-4823-B436-18F0417EEBD0 2
E14B2558-F8B4-4660-967C-2EBC452BE881 0
208B46F8-B39D-4BC8-A907-57A07CD0A380 1
76275D64-AFD9-425C-8BD5-9FA770C24AA5 1
EA092967-680B-4EA6-A137-CD9834C51323 2
6C346BA1-1D3D-410A-A1A3-CF0B2F840C0E 1
EC68A93E-996F-4D4E-B443-DE63BD9B9F59 1
06C9A62D-9790-4080-8863-F7F2D0432F67 1
428D6C3F-57AF-4F6C-AE7F-F8C5D10AA6A5 2
DF027EE0-FAA3-4DA2-844A-F94AB6D56C2C 1
C82E60DF-7193-4099-B273-FE497B3002E5 1
```

However, what I then want to do is turn this into a single row of output, with columns for each distinct `NbrPlayers`

value. As I know that I have `0`

, `1`

, and `2`

in this output, I can do this with `PIVOT`

like so:

```
SELECT
[0] AS [0pGames], [1] AS [1pGames], [2] AS [2pGames]
FROM (
SELECT
gm.[Id]
,SUM(CASE WHEN mbr.[UserType] = 1 THEN 1 ELSE 0 END) AS NbrPlayers
FROM
dbo.[Game] gm
LEFT JOIN dbo.[GameMembership] mbr ON gm.[Id] = mbr.[GameId]
WHERE
CAST(gm.[DateCreated] AS DATE) = CAST('2023-04-30' AS DATE)
GROUP BY
gm.[Id]
) x
PIVOT (
COUNT([Id])
FOR [NbrPlayers] IN ([0], [1], [2])
) pvt
```

Which results in:

```
0pGames 1pGames 2pGames
=============================
1 7 3
```

This problem is, what happens when I get other `NbrPlayers`

values, and I don’t necessarily know in advance what they might be? What’s more, I actually don’t want a separate column for each one of those other values; I want one column that sums up "everything else". Say I had a couple of entries for `NbrPlayers`

being `3`

, and a couple where `NbrPlayers`

was `7`

. I’d want an output like:

```
0pGames 1pGames 2pGames AllOtherGames
==============================================
1 7 3 4
```

As far as I can tell, `PIVOT`

doesn’t have this "catch-all" functionality that can be specified in the `FOR`

clause, so how can I achieve this?

### >Solution :

You can do the pivot manually, in other words use conditional aggregation

```
SELECT
[0pGames] = COUNT(CASE WHEN NbrPlayers = 0 THEN 1 END),
[1pGames] = COUNT(CASE WHEN NbrPlayers = 1 THEN 1 END),
[2pGames] = COUNT(CASE WHEN NbrPlayers = 2 THEN 1 END),
AllOtherGames = COUNT(CASE WHEN NbrPlayers NOT IN (0, 1, 2) THEN 1 END)
FROM (
SELECT
gm.Id,
COUNT(CASE WHEN mbr.UserType = 1 THEN 1 END) AS NbrPlayers
FROM dbo.Game gm
LEFT JOIN dbo.GameMembership mbr ON gm.Id = mbr.GameId
WHERE gm.DateCreated >= '20230430'
AND gm.DateCreated < '20230501'
GROUP BY
gm.Id
) gm;
```

Note the use of a date range, rather than casting to date which is inefficient.

Also, your main query seems questionable. You are using a left-join, but then filtering in the `COUNT`

. You could equally filter inside the left-join, then just count the number of matches.

```
SELECT
[0pGames] = COUNT(CASE WHEN NbrPlayers = 0 THEN 1 END),
[1pGames] = COUNT(CASE WHEN NbrPlayers = 1 THEN 1 END),
[2pGames] = COUNT(CASE WHEN NbrPlayers = 2 THEN 1 END),
AllOtherGames = COUNT(CASE WHEN NbrPlayers NOT IN (0, 1, 2) THEN 1 END)
FROM (
SELECT
gm.Id,
COUNT(mbr.GameId) AS NbrPlayers
FROM dbo.Game gm
LEFT JOIN dbo.GameMembership mbr ON gm.Id = mbr.GameId
AND mbr.UserType = 1
WHERE gm.DateCreated >= '20230430'
AND gm.DateCreated < '20230501'
GROUP BY
gm.Id
) gm;
```