如何合并三个COUNT查询并将结果展示为独立列？

The issue with your original query is that UNION ALL stacks results vertically—so you're getting three rows per id (one for each count) instead of one row with three columns. To achieve your desired output, you need to use conditional aggregation to calculate all three counts in a single query, rather than splitting them into separate queries and unioning.

Solution 1: Using Your Unpivoted Subquery (Simplified) #

This builds on your original approach of converting the s1 to s5 columns into a single val column, but combines all three count calculations into one SELECT:

SELECT
    id,
    SUM(val = 3) AS valcount3,
    SUM(val = 2) AS valcount2,
    SUM(val = 1) AS valcount1
FROM (
    SELECT id, s1 AS val FROM t1
    UNION ALL
    SELECT id, s2 AS val FROM t1
    UNION ALL
    SELECT id, s3 AS val FROM t1
    UNION ALL
    SELECT id, s4 AS val FROM t1
    UNION ALL
    SELECT id, s5 AS val FROM t1
) sub
GROUP BY id;

How this works: #

• The subquery turns every value from s1-s5 into a separate row with a val column.
• In most SQL dialects (like MySQL), boolean expressions like val = 3 evaluate to 1 (true) or 0 (false). Summing these values gives the total number of times 3 appears across all s columns for each id.
• Grouping by id ensures all counts are rolled up into a single row per unique id.

Solution 2: Direct Aggregation Without Unpivoting (More Efficient) #

If you want to skip the UNION ALL subquery (which can slow down large datasets), you can calculate counts directly by checking each column individually:

SELECT
    id,
    SUM(s1 = 3) + SUM(s2 = 3) + SUM(s3 = 3) + SUM(s4 = 3) + SUM(s5 = 3) AS valcount3,
    SUM(s1 = 2) + SUM(s2 = 2) + SUM(s3 = 2) + SUM(s4 = 2) + SUM(s5 = 2) AS valcount2,
    SUM(s1 = 1) + SUM(s2 = 1) + SUM(s3 = 1) + SUM(s4 = 1) + SUM(s5 = 1) AS valcount1
FROM t1
GROUP BY id;

How this works: #

• Each SUM(sX = N) counts how many times value N shows up in column sX for the id.
• Adding these sums together gives the total occurrences of N across all s1-s5 columns per id.
• This method is faster because it avoids scanning the table multiple times (once per UNION ALL branch in the first solution).

Why Your Original Query Failed #

Your original query ran three separate aggregations and used UNION ALL to stack the results. This created three rows per id (one for each count column) instead of merging them into a single row with all three counts. By combining all aggregations into one SELECT with GROUP BY id, you get the side-by-side columns you need.

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