子查询返回操作数需含1列问题排查及SQL语句修正咨询

Hey there! Let's figure out why you're getting that Operand should contain 1 column(s) error and fix your SQL query right away.

The core issue here is super straightforward: the subquery you embedded in your main SELECT clause is returning 3 columns (id_tech, name, quantity), but the main query expects any subquery in the SELECT list to return only a single column. Think of it this way—each spot in your SELECT clause is a single "slot" for one piece of data; you can't stuff three different values into that one slot.

Looking at your original query, that inner subquery is trying to output multiple pieces of information all at once where the main query only expects one value, which triggers the error.

First, I'll assume your goal is to:

1. Count total scheduled tasks per id_tech from tbl_calendar
2. Count how many of those tasks have timeout records in tbl_service_report
3. Pull the associated client name for each tech

I've restructured your query using JOINs instead of problematic multi-column subqueries. I also fixed what looks like a possible typo (you joined tbl_calendar.id_tech to tbl_client.id_client—if that's intentional, just swap it back):

SELECT
    tc.id_tech,
    COUNT(tc.id_tech) AS scheduled,
    COALESCE(tsr.timeout_quantity, 0) AS timeout_quantity,
    COALESCE(tc2.c_name, 'No Client Assigned') AS client_name
FROM
    db_pm.tbl_calendar tc
LEFT JOIN (
    -- Subquery to count timeout tasks per tech/client
    SELECT
        tc_inner.id_tech,
        tc_inner.id_client,
        COUNT(tc_inner.id_tech) AS timeout_quantity
    FROM
        db_pm.tbl_calendar tc_inner
    LEFT JOIN
        db_pm.tbl_service_report tsr_inner 
        ON tsr_inner.id_service_case = tc_inner.id_service_case
    WHERE
        tsr_inner.timeout != ''
    GROUP BY
        tc_inner.id_tech, tc_inner.id_client
) tsr 
ON tc.id_tech = tsr.id_tech AND tc.id_client = tsr.id_client
LEFT JOIN
    db_pm.tbl_client tc2 
    ON tc.id_client = tc2.id_client
GROUP BY
    tc.id_tech, tsr.timeout_quantity, tc2.c_name;

• Replaced multi-column subquery: I moved the timeout count logic into a separate, grouped subquery that returns one aggregated value per tech/client, then joined it back to the main query. This avoids the "multiple columns in single slot" issue.
• Handled NULLs: Used COALESCE to replace NULL values (for techs with no timeouts or no assigned client) with user-friendly defaults (0 or "No Client Assigned").
• Cleaned up grouping: Ensured all non-aggregated fields are included in the GROUP BY clause to comply with standard SQL rules.

Alternative: Single-Column Subqueries (If You Prefer) #

If you really want to use subqueries in the SELECT clause, you need to split them into separate single-column subqueries like this:

SELECT
    tc.id_tech,
    COUNT(tc.id_tech) AS scheduled,
    -- Subquery for timeout count (single column)
    (SELECT COUNT(*) 
     FROM db_pm.tbl_calendar tc_inner 
     JOIN db_pm.tbl_service_report tsr_inner 
     ON tsr_inner.id_service_case = tc_inner.id_service_case
     WHERE tc_inner.id_tech = tc.id_tech 
       AND tsr_inner.timeout != '') AS timeout_quantity,
    -- Subquery for client name (single column)
    (SELECT tc2.c_name 
     FROM db_pm.tbl_client tc2 
     WHERE tc2.id_client = tc.id_client) AS client_name
FROM
    db_pm.tbl_calendar tc
GROUP BY
    tc.id_tech;

Note that this approach can be slower for large datasets compared to the JOIN method, so the first solution is generally better.

内容的提问来源于stack exchange，提问作者Paz Cruz