I have a base dataset formatted like the one below.
Sex1 | Sex2 | Number| F | | 5 | M | | 8 | M | F | 5 | F | F | 3 | M | F | 2 | F | M | 1 | F | F | 9 | M | M | 3 |
I'd like to use SQL to create a query that produces totals from the price by Gender composition like so:
Sex | Total Number F | 5 M | 8 MF | 8 FF | 12 MM | 3
However, I'm struggling to find a way to pull the FM and MF categories into the same row. The code I have that leaves them distinct is of the form
SELECT Sex1 & "" & Sex2 AS Sex, Sum(Number) AS [Total Number] FROM TableName GROUP BY Sex1 & "" & Sex2
Is there a simple way to combine the rows?