How we write Group By clause in Inner Joins in multiple table

Posted by Bageshkumarbagi under Sql Server on 6/21/2013 | Points: 10 | Views : 3291 | Status : [Member] | Replies : 6
Hi,

I want to Generate a report for that i am using multiple table but when i am using Group by clause i am getting Error like 'Column Name ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks,
Bagesh Kumar Singh

Thanks,
Bagesh Kumar Singh
Mark as answer if you have been satisfied



Responses

Posted by: aswinialuri-19361 on: 6/21/2013 [Member] Starter | Points: 25

Up
0
Down
hi try this link
I hope it will help you
http://stackoverflow.com/questions/13344732/select-query-with-group-by-clause-and-multiple-inner-joins-in-postresql

http://www.codeproject.com/Tips/57496/SQL-Group-by-With-Joins

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Bageshkumarbagi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: aswinialuri-19361 on: 6/21/2013 [Member] Starter | Points: 25

Up
0
Down
hi try this link
I hope it will help you
http://stackoverflow.com/questions/13344732/select-query-with-group-by-clause-and-multiple-inner-joins-in-postresql

http://www.codeproject.com/Tips/57496/SQL-Group-by-With-Joins

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Bageshkumarbagi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jitendrasoft09 on: 6/21/2013 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi,
It is nice question, hope you are trying well, you have to check as mentioned in above link you have to create alias for both table and need to access the column name with alias or whichever table you have used any functions in that column or another column for same table, you have to mention the group by clause.




Jitendra Kumar
If my post helps you, plz mark as an answer.

Bageshkumarbagi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bageshkumarbagi on: 6/21/2013 [Member] Starter | Points: 25

Up
0
Down

Thanks to all..


Thanks,
Bagesh kumar singh

Thanks,
Bagesh Kumar Singh
Mark as answer if you have been satisfied

Bageshkumarbagi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 6/24/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Bageshkumarbagi,

The Error is due to Either you missed to give alias name or column not mentioned in group by clause

select a.Column1,a.Column2,b.Column3,sum(b.column4)

from Table1 a join Table2 b on a.id=b.id
group by a.Column1,a.Column2

The above query will not give the output. The Correct form of above query is

select a.Column1,a.Column2,b.Column3,sum(b.column4)Column4

from Table1 a join Table2 b on a.id=b.id
group by a.Column1,a.Column2,b.Column3


The mistakes are

1)while using aggregate function there should be an alias name.
sum(b.column4)-->sum(b.column4)Column4

2)Column without aggregate function should be mentioned in group by clause



Bageshkumarbagi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/24/2013 [Member] Starter | Points: 25

Up
0
Down
The Error Says :
'Column Name ' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

first let me tell you It has nothing to do with join ,

check the example below





Create table #ProductSale (ProductId char(1) , customerID int , SaleQty int)
Insert Into #ProductSale
Select 'A' , 101 , 10 union All
Select 'A' , 44 , 2 union All
Select 'A' , 3 , 1 union All
Select 'B' , 44 , 3 union All
Select 'c' , 3 , 5 union All
Select 'c' , 4 , 1




Select ProductId , Sum(SaleQty) SaleQty From #ProductSale
Group by customerID
Column ProductId in not in group by clause

Msg 8120, Level 16, State 1, Line 1
Column '#ProductSale.ProductId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

http://www.tutorialsqlserver.com/




Bageshkumarbagi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response