添加COUNT聚合函数后SQL执行报错：branch_id未在聚合或GROUP BY中

Hey there, let's fix that SQL error right away!

The error you're seeing is a classic one when using aggregate functions like COUNT(): any column you include in your SELECT clause that isn't wrapped in an aggregate function must be listed in the GROUP BY clause. This tells the database how to group rows together before calculating the aggregated count.

Corrected SQL Code #

SELECT 
    c.branch_id, 
    d.DVD_genre, 
    COUNT(c.DVD_catalogno) AS Total_DVD 
FROM CatalogNo_No c 
INNER JOIN DVD d ON c.DVD_catalogno = d.DVD_catalogno 
-- 注意：你原WHERE子句里的`d.DVD_catalogno = d.DVD_genre`看起来不太合理
-- 如果是想筛选特定类型的DVD，可以改成类似：WHERE d.DVD_genre = '动作片'
GROUP BY c.branch_id, d.DVD_genre  -- 这里添加了所有非聚合列
ORDER BY c.branch_id;

问题解析 #

• GROUP BY 缺失：你的查询里branch_id和DVD_genre都是非聚合列，数据库需要明确知道按照哪些维度分组统计数量，所以必须把这两个列加入GROUP BY子句。
• WHERE子句疑点：d.DVD_catalogno（通常是编号/ID）和d.DVD_genre（类型名称）属于不同类型的数据，直接相等的条件大概率是笔误。如果是要筛选特定类型的DVD，建议调整为类似WHERE d.DVD_genre = '科幻片'这样的条件。

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