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.
select Id, name,dayA,monthA,yearA,
sum(x) as x,
(select SUM(x) group by month) as total,
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
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