嵌套GROUP BY的SUM求和实现及SQL查询结果异常咨询

Alright, let's break down why your current query is returning total row counts for each NAMES instead of the targeted sums for each PART value, and how to fix it.

Why Your Current Query Isn't Working #

The issue here is how your database handles the boolean expression PART = "F001" inside the SUM() function. Many SQL databases don't automatically convert boolean results (TRUE/FALSE) into numeric values (1/0) for aggregation. Instead, they might treat all boolean values as 1 when calculating SUM(), which ends up giving you the total number of rows per NAMES instead of the count of rows where PART matches your target value.

The Universal Fix: Using CASE WHEN #

This approach works across nearly all SQL databases (MySQL, PostgreSQL, SQL Server, Oracle, etc.). We explicitly define what value to sum when the condition is met:

SELECT 
    NAMES,
    SUM(CASE WHEN PART = 'F001' THEN 1 ELSE 0 END) AS SUM_F001,
    SUM(CASE WHEN PART = 'F002' THEN 1 ELSE 0 END) AS SUM_F002,
    SUM(CASE WHEN PART = 'F003' THEN 1 ELSE 0 END) AS SUM_F003
FROM MY_TABLE
GROUP BY NAMES
ORDER BY NAMES ASC;

Here's what's happening:

• For each row, the CASE WHEN checks if PART matches the target value. If yes, it returns 1; if not, 0.
• SUM() then adds up all those 1s and 0s, giving you the exact count of rows per NAMES where PART equals each target value.

Database-Specific Shortcuts #

If you're using a specific database, you can use more concise syntax:

MySQL/MariaDB: Use IF() Function

MySQL and MariaDB have a built-in IF() function that simplifies conditional logic:

SELECT 
    NAMES,
    SUM(IF(PART = 'F001', 1, 0)) AS SUM_F001,
    SUM(IF(PART = 'F002', 1, 0)) AS SUM_F002,
    SUM(IF(PART = 'F003', 1, 0)) AS SUM_F003
FROM MY_TABLE
GROUP BY NAMES
ORDER BY NAMES ASC;

PostgreSQL: Cast Boolean to Integer

PostgreSQL lets you cast boolean results directly to integers (TRUE becomes 1, FALSE becomes 0):

SELECT 
    NAMES,
    SUM((PART = 'F001')::INT) AS SUM_F001,
    SUM((PART = 'F002')::INT) AS SUM_F002,
    SUM((PART = 'F003')::INT) AS SUM_F003
FROM MY_TABLE
GROUP BY NAMES
ORDER BY NAMES ASC;

Quick Note on Sorting #

You might have noticed I moved ASC to an ORDER BY clause instead of keeping it in GROUP BY. While some databases allow sorting in GROUP BY, it's not standard SQL and can lead to inconsistent behavior. Using ORDER BY is the reliable, cross-database way to sort your results.

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