PostgreSQL使用CASE语句分组报错：列需在GROUP BY或聚合函数中

Hey there! Let's break down why that error is popping up and get your query working so you get one row per pd.id.

Why the Error Happens #

PostgreSQL (which this error looks like it's coming from) enforces strict SQL standards for GROUP BY clauses. When you group by a column like pd.id, every column in your SELECT list either needs to:

• Be included in the GROUP BY clause, or
• Be wrapped in an aggregate function (like MAX(), MIN(), FIRST_VALUE()) that tells the database how to pick a value from multiple rows in the group.

Your pppd.created_at and pppd.converted_at columns aren't in either category right now, so the database doesn't know which value to pick for each pd.id group.

Solutions Based on Your Data #

Let's cover the two most common scenarios you're likely dealing with:

1. Each pd.id has exactly one matching pppd record

If every pd entry links to only one pppd row, just add all your non-aggregated columns to the GROUP BY clause. This works because there's no ambiguity—each group only has one value for each column.

Here's how your query would look:

SELECT 
    pd.id, 
    pd.npi, 
    pppd.created_at AS "date_submitted", 
    pppd.converted_at AS "date_approved",
    -- Your existing CASE statements for specialties
    CASE 
        -- Insert your primary_specialty logic here
    END AS primary_specialty,
    CASE 
        -- Insert your secondary_specialty logic here
    END AS secondary_specialty
FROM 
    pd
JOIN 
    pppd ON -- Add your join condition here (e.g., pd.id = pppd.pd_id)
GROUP BY 
    pd.id, pd.npi, pppd.created_at, pppd.converted_at;

2. Each pd.id has multiple pppd records (need to pick a specific row)

If a single pd links to multiple pppd entries, you'll need to tell the database which date values to use. For example, if you want the most recent submission/approval date, use aggregate functions like MAX():

SELECT 
    pd.id, 
    pd.npi, 
    MAX(pppd.created_at) AS "date_submitted", 
    MAX(pppd.converted_at) AS "date_approved",
    CASE 
        -- Insert your primary_specialty logic here
    END AS primary_specialty,
    CASE 
        -- Insert your secondary_specialty logic here
    END AS secondary_specialty
FROM 
    pd
JOIN 
    pppd ON -- Add your join condition here
GROUP BY 
    pd.id, pd.npi;

If you need more control (like picking the first submitted record, or the most recently approved one), use a window function like ROW_NUMBER() to rank records per pd.id:

WITH ranked_pppd AS (
    SELECT 
        pd.id, 
        pd.npi, 
        pppd.created_at AS "date_submitted", 
        pppd.converted_at AS "date_approved",
        CASE 
            -- Insert your primary_specialty logic here
        END AS primary_specialty,
        CASE 
            -- Insert your secondary_specialty logic here
        END AS secondary_specialty,
        -- Rank records by submission date (newest first)
        ROW_NUMBER() OVER (PARTITION BY pd.id ORDER BY pppd.created_at DESC) AS record_rank
    FROM 
        pd
    JOIN 
        pppd ON -- Add your join condition here
)
SELECT 
    id, npi, date_submitted, date_approved, primary_specialty, secondary_specialty
FROM 
    ranked_pppd
WHERE 
    record_rank = 1; -- Only keep the top-ranked record per pd.id

This window function approach lets you precisely select which row to keep for each pd.id without relying on aggregate functions.

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