PostgreSQL：多列去重计数（附带其他字段查询）

Got it, let's fix that PostgreSQL query of yours. The main issues are a syntax quirk with COUNT(DISTINCT) and a missing GROUP BY clause—here's how to sort it out:

Core Problems in Your Original Query #

1. PostgreSQL doesn't support COUNT(DISTINCT col1, col2)
   Unlike some other databases, PostgreSQL only accepts a single expression inside COUNT(DISTINCT). Passing multiple columns directly throws a syntax error.
2. Missing GROUP BY clause
   You're using an aggregate function (COUNT()) but haven't specified which columns to group results by. PostgreSQL's default settings require all non-aggregated columns to be included in GROUP BY (or functionally dependent on grouped columns).

Fixing the COUNT(DISTINCT) Syntax #

When you need to count distinct combinations of multiple columns, use one of these reliable methods:

• Option 1: Use a ROW() constructor
  Wrap the columns in ROW() to treat them as a single composite value. This is the cleanest, safest approach:

  COUNT(DISTINCT ROW(class_enrollment.semester_code, class_enrollment.class_code))
  

• Option 2: Concatenate columns with a unique separator
  If your column values don't contain the separator (e.g., |), you can concatenate them into a single string. Avoid this if your data might include the separator—it could create false duplicates:

  COUNT(DISTINCT CONCAT(class_enrollment.semester_code, '|', class_enrollment.class_code))
  

Full Corrected Query #

Assuming your goal is to get each course offering's details along with the number of distinct enrollment records and maximum capacity, here's the polished query (I added table aliases for readability):

SELECT
  co.semester_code AS SC,
  co.class_code AS CC,
  co.class_name AS CN,
  co.teacher_name AS TN,
  COUNT(DISTINCT ROW(ce.semester_code, ce.class_code)) AS num_enrolled,
  ce.maximum_capacity AS max_enrollment
FROM class_offerings co
NATURAL JOIN class_enrollment ce
GROUP BY co.semester_code, co.class_code, co.class_name, co.teacher_name, ce.maximum_capacity;

Quick Optimization Note #

Wait a second—since you're using NATURAL JOIN, co.semester_code and ce.semester_code are identical (same with class_code). Counting distinct combinations of these columns is redundant if each enrollment is tied to one course offering. If you actually want to count unique students enrolled in each course, replace the COUNT() line with:

COUNT(DISTINCT ce.student_id) AS num_enrolled

(Just swap student_id for whatever the primary key column is in your class_enrollment table.)

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