我有两个表(学生和帐户)
每个学生在学生表中都有一条记录,但可能在帐户表中有一条以上的记录。
我需要对帐户表中的一列进行汇总,并按学生表中的学生代码分组
我已经编写了以下查询,但是当我使用GROUP BY命令时,它返回了一个错误:
select students.id,students.stcode,students.stname,account.stcode,
sum(cast ((account.price) AS INT)) OVER () AS PriceTotal
from students
inner join account on students.stcode=account.stcode
group by students.stcode
错误消息:
Column 'students.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
。
您需要按不属于聚合的所有列进行分组。就是说,如果students.stname在功能上依赖于students.stcode(即students.stcode是唯一的),则可以将一个诸如max的聚合应用到students.stcode上。由于account.stcode = students.stcode可以删除。我不确定over子句在这里应该做什么,所以我删除了那一个,留给我们:
select students.stcode, max(students.stname) as stname
, sum(cast (account.price AS INT)) AS PriceTotal
from students
join account
on students.stcode=account.stcode
group by students.stcode
另一种选择是在group by子句中包含students.stname:
select students.stcode, students.stname
, sum(cast (account.price AS INT)) AS PriceTotal
from students
join account
on students.stcode=account.stcode
group by students.stcode, students.stname
如果您使用支持窗口功能的DBMS(不使用标记了帖子的MySQL),则不需要按以下条件分组:
select students.stcode, students.stname
, sum(cast (account.price AS INT)) over () AS PriceTotal
from students
join account
on students.stcode=account.stcode
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句