VJ22 VJ22 - 6 months ago 16
MySQL Question

Query to Get Counts of Ids under 2 different Conditions with Case When Statement

So I Have a Table Structure like the one below.

Sample Current DB View

Here are the Conditions:


  1. I call 'Tag' as the IDs with none of Condition(x) as NULL.

  2. I call 'UnTag' as the IDs with any of the Condition(x) is NULL.

  3. I want to Classify 'Classification1' in the below Format:



New Classification Using CASE WHEN Statement

I want the output in the following structure:

Final Output

I wrote a code but somehow it is not giving me the desired result.
I Used Case when to Classify the Classification1 and then ran a sub-query for taking out Count.
Please help if there is a different Approach and how to incorporate the Case when in Group by statement.

I wrote this query:
SELECT
CASE
WHEN UPPER(CM1.Classification1) IN ('A', 'C','G') THEN 'X'
WHEN UPPER(CM1.Classification1) IN ('B', 'F') THEN 'Y'
WHEN UPPER(CM1.Classification1) IN ('D', 'E') THEN 'Z'
ELSE 'Undefined' END AS New_Classification,
(SELECT
COUNT(CASE
WHEN UPPER(CM2.Classification1) IN ('A', 'C','G') THEN 'X'
WHEN UPPER(CM2.Classification1) IN ('B', 'F') THEN 'Y'
WHEN UPPER(CM2.Classification1) IN ('D', 'E') THEN 'Z'
ELSE 'Undefined' END)
FROM TABLE1 CM2 WHERE
CM2.Condition1 IS NOT NULL AND
CM2.Condition2 IS NOT NULL AND
CM2.Condition3 IS NOT NULL AND
CM2.Condition4 IS NOT NULL AND
CM1.ID=CM2.ID) AS Count_of_tagged,
(SELECT
COUNT(CASE
WHEN UPPER(CM3.Classification1) IN ('A', 'C','G') THEN 'X'
WHEN UPPER(CM3.Classification1) IN ('B', 'F') THEN 'Y'
WHEN UPPER(CM3.Classification1) IN ('D', 'E') THEN 'Z'
ELSE 'Undefined' END)
FROM TABLE1 CM3 WHERE
(CM3.Condition1 IS NULL OR
CM3.Condition2 IS NULL OR
CM3.Condition3 IS NULL OR
CM3.Condition4 IS NULL) AND
CM1.ID=CM2.ID) AS Count_of_untagged
FROM TABLE1 CM1

Answer

You shouldn't need a subquery, as a starting point I came up with:

    SELECT CASE Classification1
             WHEN 'A' THEN 'X'
             WHEN 'B' THEN 'Y'
             WHEN 'C' THEN 'X'
             WHEN 'D' THEN 'Z'
             WHEN 'E' THEN 'Z'
             WHEN 'F' THEN 'Y'
             WHEN 'G' THEN 'X'
           END new_classification,
           SUM(
             Condition1 IS NOT NULL AND
             Condition2 IS NOT NULL AND
             Condition3 IS NOT NULL AND
             Condition4 IS NOT NULL  /* 1 for true, 0 for false */          
           ) tagged_count,
           SUM(
             Condition1 IS NULL OR
             Condition2 IS NULL OR
             Condition3 IS NULL OR
             Condition4 IS NULL            
           ) untagged_count,
      FROM table_name
  GROUP BY new_classification
Comments