如何解决这个错误:
无法在GROUP BY子句的GROUP BY列表中使用的表达式中使用聚合或子查询。
这是我的查询:
select Id, name,dayA,monthA,yearA,
sum(x) as x,
(select SUM(x) group by month) as total,
from table_A
group by Id,name,monthA,dAyA,yearA, SUM(x)
换句话说:样本数据:
id name dayA monthA yearA x
===========================
1 name1 2 3 2016 4
2 name2 2 3 2016 3
3 name1 2 3 2016 2
预期结果 :
id name dayA monthA yearA x total
===================================
1 name1 2 3 2016 4 6
2 name2 2 3 2016 3 3
3 name1 2 3 2016 2 6
提前致谢
您查询的问题更多。
(select SUM(x) group by month) as total
,是否来自同一张表,不太可能,因为列月份未在您的分组依据中提及。在查询中使用子查询时,必须保证我只会返回一条记录。
根据您的样本数据和预期结果...
create table table_A(
id int,
name varchar(25),
dayA int,
monthA int,
yearA int,
x int
)
insert into table_A
values (1,'name1',2,3,2016,4),
(2,'name2',2,3,2016,3),
(2,'name1',2,3,2016,2)
select ta.id, ta.name, ta.dayA, ta.monthA, ta.yearA, ta.x, total.Total from table_A as ta
left join
(select name, sum(x) as Total from table_A group by name) total on ta.name = total.name
group by
ta.id, ta.name, ta.dayA, ta.monthA, ta.yearA, ta.x, total.name, total.Total
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句