group by in case of nested cases with conditions on different tables

Question

My question is a bit similar to this question but with a caveat. In my case the conditions are dependent on different tables, not one table. The part which is giving me trouble is the GROUP BY part. Here is the query:

SELECT
    CASE 
        WHEN T1.ImportantColumn = 'Y'
        THEN 'Good'
        ELSE
            CASE
                WHEN T2.ImportantColumn = 1
                THEN 'Very Good'
                ELSE
                    CASE
                        WHEN T3.ImportantColumn IS NULL
                        THEN 'Bad'
                        ELSE T3.ImportantColumn
                    END
            END
    END AS WorkStatus,   
    SUM(case when T2.sex = 'M' THEN 1 ELSE 0 END) male , 
    SUM(case when T2.sex = 'F' THEN 1 ELSE 0 END) female , 
    COUNT(WorkStatus) AS [CountWorkStatus] 
FROM 
    Condition1Table T1 
    RIGHT JOIN Condition2Table T2 ON T1.city = T2.Code_id AND T1.field_name = 'cities' 
    INNER JOIN Condition3Table T3 ON T2.student_id = T3.student_id
GROUP BY T3.ImportantColumn, T2.ImportantColumn, T1.ImportantColumn -- <-- wrote this but I know it's wrong

It is sort of IF ELSE scenario. If Condition1Table.ImportantColumn is 'Y' then 'Good', else if Condition2Table.ImportantColumn is 1 then 'Very Good', else if Condition3Table.ImportantColumn is NULL then 'bad', Else the value in Condition3Table.ImportantColumn. The hard part is the grouping of data in a desired format which is below:

WorkStatus | male | female | CountWorkStatus
----------   -----  ------   ---------------
Good       |  3   |   7    | 10
Very Good  | 11   |   2    | 13
Bad        |  5   |   0    | 5
Val1       |  1   |   9    | 10
Val2       | 41   |   23   | 64

No need to nest. Simply put the different WHEN in the same case expression. — jarlh, Sep 04 '18 at 13:17

score 0 · Answer 1 · answered Sep 04 '18 at 13:20

You seem to be asking "how do I group by a huge CASE statement without repeating the whole CASE statement"?

If so, just use a sub-query.

Then the result of the CASE statement has a column name that you can refer to.

There is near zero performance penalty here, sub-queries are expanded out macro-like. SQL is a declarative language, it's just a syntax for expressing a problem to be solved. When that's compiled down there's a program to run. So, while thinking about the SQL, you just need the syntax to express your problem.

SELECT
    WorkStatus,
    SUM(case when sex = 'M' THEN 1 ELSE 0 END) male , 
    SUM(case when sex = 'F' THEN 1 ELSE 0 END) female , 
    COUNT(WorkStatus) AS [CountWorkStatus] 
FROM
(
    SELECT
        CASE 
            WHEN T1.ImportantColumn = 'Y'
            THEN 'Good'
            ELSE
                CASE
                    WHEN T2.ImportantColumn = 1
                    THEN 'Very Good'
                    ELSE
                        CASE
                            WHEN T3.ImportantColumn IS NULL
                            THEN 'Bad'
                            ELSE T3.ImportantColumn
                        END
                END
        END AS WorkStatus,
        T2.sex
    FROM 
        Condition1Table T1 
        RIGHT JOIN Condition2Table T2 ON T1.city = T2.Code_id AND T1.field_name = 'cities' 
        INNER JOIN Condition3Table T3 ON T2.student_id = T3.student_id
)
  AS StatusBySex
GROUP BY
  WorkStatus

group by in case of nested cases with conditions on different tables

1 Answers1