Magnolia Magnolia - 6 months ago 20
SQL Question

using group by in subquery in sql

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

Answer

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
Comments