PostgreSQL GROUP BY与聚合函数报错问题求助

Let's break down why you're hitting this error and fix it step by step.

Why the Error Occurs #

PostgreSQL strictly follows SQL standards for GROUP BY queries. The core rule here is: any column you include in your SELECT clause that isn't wrapped in an aggregate function (like COUNT(), SUM(), etc.) must be listed in your GROUP BY clause.

Looking at your query, you're selecting aircraft.aircraft_id, flight.health_start_date, flight.health_end_date, and flight.maintenance_flight_time—but none of these are in your GROUP BY list or aggregated. That's exactly what's triggering the ERROR [42803] message.

The Corrected Query #

Since your goal is to group results by flight date and aircraft registration number, you need to add all non-aggregated columns to your GROUP BY clause. Here's the updated code that should run without errors:

SELECT 
    "public".aircraft.aircraft_id,
    COUNT(*) AS TOTALDOWNLOAD,
    "public".aircraft.register_number,
    to_char("public".flight.flight_date, 'DDMMYYYY') as flight_date,
    to_char("public".flight.health_start_date, 'hh24:mi') as health_start_date,
    to_char("public".flight.health_end_date, 'hh24:mi') as health_end_date,
    "public".flight.maintenance_flight_time
FROM "public".flight
INNER JOIN "public".aircraft ON "public".flight.aircraft_id = "public".aircraft.id
WHERE "public".flight.maintenance_flight_time > 0
GROUP BY 
    "public".flight.flight_date,
    "public".aircraft.register_number,
    "public".aircraft.aircraft_id,
    "public".flight.health_start_date,
    "public".flight.health_end_date,
    "public".flight.maintenance_flight_time

A Quick Optimization Tip #

If aircraft.register_number is a unique value directly tied to aircraft.aircraft_id (which makes sense for a registration number), you could simplify the GROUP BY by only including aircraft.aircraft_id and flight.flight_date. PostgreSQL recognizes that other aircraft columns are functionally dependent on the primary key, so it won't throw an error. But including all columns explicitly is safer, especially if you're working with older PostgreSQL versions or if there's any chance registration numbers aren't unique.

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