0

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?

0

You could use a CASE expression or the IIf() function to first order the two values and then group by them:

with CASE expression:

SELECT CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END 
       & CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END,
         CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END ;

with IIf() function:

SELECT IIf(Sex1 < Sex2, Sex1, Sex2) & IIf(Sex1 < Sex2, Sex2, Sex1) AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY IIf(Sex1 < Sex2, Sex1, Sex2),
         IIf(Sex1 < Sex2, Sex2, Sex1) ;
0

You can use a UNION query to force the Sex1/Sex2 pairs to be in the same order

    SELECT 
        Nz(Sex1, "") & Nz(Sex2, "") AS Sex,
        Number
    FROM TableName
    WHERE Nz(Sex1, "") >= Nz(Sex2, "")
UNION ALL
    SELECT 
        Nz(Sex2, "") & Nz(Sex1, "") AS Sex,
        Number
    FROM TableName
    WHERE Nz(Sex1, "") < Nz(Sex2, "")

producing

Sex  Number
---  ------
FF        3
MF        1
FF        9
MM        3
F         5
M         8
MF        5
MF        2

and then use that as a subquery to produce the totals

SELECT
    Sex,
    SUM(Number) AS [Total Number]
FROM
    (
            SELECT 
                Nz(Sex1, "") & Nz(Sex2, "") AS Sex,
                Number
            FROM TableName
            WHERE Nz(Sex1, "") >= Nz(Sex2, "")
        UNION ALL
            SELECT 
                Nz(Sex2, "") & Nz(Sex1, "") AS Sex,
                Number
            FROM TableName
            WHERE Nz(Sex1, "") < Nz(Sex2, "")
    )
GROUP BY Sex

producing

Sex  Total Number
---  ------------
F               5
FF             12
M               8
MF              8
MM              3
-1

You can try using CONCAT() function in MSSQL to get 2 columns in one.

SELECT CONCAT(Sex1,Sex2) AS Sex, Sum(Number) AS Total_Number FROM db.test GROUP BY concat(Sex1,Sex2);

  • Does Access have a CONCAT() function? Even if it did, the group by you have would not solve the issue asked. MF would be grouped separately from FM. – ypercubeᵀᴹ Jul 18 '16 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.