MySQL报错‘Operand should contain 1 column(s)’：多列统计次数并NULL计为0

Hey there! Let's break down why your SQL is throwing that "Operand should contain 1 column(s)" error and fix it up properly.

What's Wrong With Your Original Query? #

The core issue is that the subquery inside IFNULL() returns two columns (id and count(*) as v), but IFNULL() only accepts a single column or value as its first argument. On top of that, your overall structure doesn’t correctly handle grouping results by id while ensuring NULL counts show up as 0.

Fix 1: Conditional Aggregation (Simpler Approach) #

Instead of using UNION ALL to "unpivot" columns, you can calculate the count directly with conditional logic. This method is cleaner and faster for a fixed set of columns:

SELECT 
    id,
    COALESCE(s1 = 3, 0) +
    COALESCE(s2 = 3, 0) +
    COALESCE(s3 = 3, 0) +
    COALESCE(s4 = 3, 0) +
    COALESCE(s5 = 3, 0) AS Valcount
FROM t1;

• How it works: In MySQL, a boolean expression like s1 = 3 returns 1 if true, 0 if false, and NULL if s1 is NULL. COALESCE() converts those NULL values to 0, so adding them up gives the total number of times 3 appears across the columns for each id.

If you prefer explicit, easy-to-read logic, this equivalent query uses IF statements instead:

SELECT 
    id,
    IF(IFNULL(s1, '') = 3, 1, 0) +
    IF(IFNULL(s2, '') = 3, 1, 0) +
    IF(IFNULL(s3, '') = 3, 1, 0) +
    IF(IFNULL(s4, '') = 3, 1, 0) +
    IF(IFNULL(s5, '') = 3, 1, 0) AS Valcount
FROM t1;

Fix 2: Unpivot with UNION ALL (Great for Many Columns) #

If you have tons of columns and don’t want to write conditional logic for each, use UNION ALL to turn columns into rows, then aggregate. We’ll use a LEFT JOIN to make sure even ids with no occurrences of 3 get a count of 0:

SELECT 
    t.id,
    COALESCE(sub.v, 0) AS Valcount
FROM t1 t
LEFT JOIN (
    SELECT id, COUNT(*) AS v
    FROM (
        SELECT id, s1 AS val FROM t1
        UNION ALL
        SELECT id, s2 FROM t1
        UNION ALL
        SELECT id, s3 FROM t1
        UNION ALL
        SELECT id, s4 FROM t1
        UNION ALL
        SELECT id, s5 FROM t1
    ) sub
    WHERE val = 3
    GROUP BY id
) sub ON t.id = sub.id;

• How it works: The inner UNION ALL converts each column value into a separate row. We filter for val = 3, group by id to count occurrences, then LEFT JOIN back to the original table. COALESCE() turns any NULL counts (from ids with no 3s) into 0.

内容的提问来源于stack exchange，提问作者Ada S