Netezza SQL问题：按年度、奖学金及院系统计已缴停车罚单数量

问题详情 #

表结构及数据 #

student_id scholarship faculty year_parking_ticket_received year_parking_ticket_paid
1            1           0       1                         2015                     2022
2            1           0       1                         2016                     2017
3            1           0       1                         2017                     2022
4            1           0       1                         2018                     2019
5            1           0       1                         2019                     2018
6            1           0       1                         2020                     2023
7            1           0       1                         2021                     2022
8            1           0       1                         2022                     2023
9            1           0       1                         2023                     2015
10           2           0       1                         2015                     2016
11           2           0       1                         2016                     2023
12           2           0       1                         2017                     2021
13           2           0       1                         2018                     2015
14           2           0       1                         2019                     2023

需求 #

统计每一年中，每个奖学金（scholarship）和院系（faculty）组合下的已缴停车罚单数量，结果格式示例：

scholarship faculty year_parking_ticket_paid number_of_tickets_paid
1         1    1                     2010                     15
2         1    1                     2011                     19
3         1    1                     2012                    22

错误SQL及报错 #

用户尝试的SQL：

select sum_schol, sum_fac, number_of_tickets, count(*)
from
(select student_id, year_parking_ticket_paid, sum(scholarship) as sum_schol, sum(faculty) as sum_fac, count(*) as number_of_tickets
from my_table
group by student_id, number_of_tickets) as B
group by student_id, year_parking_ticket_paid, sum_schol, sum_fac

报错信息：GROUP BY clause cannot contain aggregates!

补充说明 #

• 同一学生在同一年的奖学金和院系不会变更；
• 仅关注学生缴纳罚单年份的奖学金和院系信息，无需考虑罚单接收年份；
• 同一学生不同年份的奖学金和院系可能不同；
• scholarship取值仅为0或1，faculty取值仅为0或1。

修正后的SQL #

select 
    scholarship,
    faculty,
    year_parking_ticket_paid,
    count(*) as number_of_tickets_paid
from my_table
group by scholarship, faculty, year_parking_ticket_paid
order by year_parking_ticket_paid, scholarship, faculty;

错误原因及说明 #

1. 原SQL错误点：子查询的GROUP BY子句中使用了聚合列number_of_tickets，SQL语法要求GROUP BY只能包含原始表的非聚合字段或分组键，不能直接引用聚合函数生成的别名。
2. 需求匹配：根据补充说明，同一学生同一年的scholarship和faculty值固定，因此直接按scholarship、faculty、year_parking_ticket_paid三个字段分组，用count(*)统计每组的罚单数量即可满足需求，无需额外子查询分组逻辑。

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