select
        sum(0 +
        case
            when
                coalesce(course,'') like '%course3%'
                    then 1
                    else 0
        end +
        case
            when
                coalesce(course,'') like '%course2%'
                    then 1
                    else 0
        end +
        case
            when
                coalesce(course,'') like 'course1%'
                    then 1
                    else 0
        end) as staff_nums
    from
        cultivate_tb
  • SUM: Aggregates the total count across rows.
  • 0 +: Acts as a base value for each row. It ensures the addition starts from 0, and then conditions add 1 if they evaluate to TRUE.
  • CASE WHEN ... THEN ... ELSE ... END: Evaluates conditions and returns 1 if the condition is met; otherwise, it returns 0.
  • COALESCE(course, ''): Replaces any NULL values in the course column with an empty string ('') to avoid errors when using LIKE.

2. CASE Conditions

Each CASE statement checks whether the value in the course column matches specific patterns:

  1. First CASE:LIKE '%course3%' checks if the string 'course3' exists anywhere in the course column (because of % wildcard on both sides).If it matches, 1 is returned; otherwise, 0.
  2. Second CASE:LIKE '%course2%' checks for 'course2' anywhere in the course column.Similar logic: 1 for a match, 0 otherwise.
  3. Third CASE:LIKE 'course1%' checks if the string starts with 'course1' (because % wildcard is only at the end).Returns 1 if it matches, 0 otherwise.

3. SUM Aggregation

The SUM() function adds up the results of the CASE expressions for all rows in the cultivate_tb table.

For each row:

  • If a condition matches, 1 is added.
  • If no condition matches, the sum remains 0 for that row.

The final result (staff_nums) is the total count of all matches across all rows.