how to get around this error :
Unable to use an aggregate or a subquery in an expression used in the GROUP BY list of a GROUP BY clause.
here is my query :
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)
in other words : sample data :
id name dayA monthA yearA x =========================== 1 name1 2 3 2016 4 2 name2 2 3 2016 3 3 name1 2 3 2016 2
Expected result :
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
Thanks in advance
you're query has more problem.
(select SUM(x) group by month) as total, is it from the same table, not likely since column month is not mention inyour group by. When using sub query in a query, you must guaranteed that i will only return one record.
Based on your sample data and expected results...
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