1

i want to join two tables with group by

select t.acno,t.name,count(Q.trans) as test,Q.transvalue,q.transdate 
from dbo.test t     
inner join dbo.testaqc q 
on t.acno=Q.acno 
group by q.trans

but i am receiving the error:

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

Fabio
  • 23,183
  • 12
  • 55
  • 64
user3325178
  • 51
  • 1
  • 1
  • 2
  • 2
    Either contain it in an aggregate function or add it to the group by clause. This is a pretty useful error message. The same goes for the other columns in your select (name, transvalue, transdate) that are not aggregated or grouped. – nathan_jr Feb 18 '14 at 20:37
  • did you google this error ? This is a VERY common error and you can get the answer easily. -1 for that. When you search errors in google, use ONLY the error code and/or the error message. Do NOT use things like column names and such. Eg. Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Steam Feb 18 '14 at 20:44
  • See bill karwins answer here - http://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e It tells you the REASON for the error and not just how to correct it and make your query run, using a simple example. – Steam Feb 18 '14 at 20:50
  • Unjustified vote downs: The question is unique in that search for 8120 in google leads directly here. All the other references that good folks have suggested here, clarify and explain but they don't directly come up when searching for 8120. You're incorrectly assuming a beginner would make the connection that's obvious to the negative voters. Voting down a beginner with a unique enough request is unkind, and hurts stackoverflow as a platform for learning. The user never came back to post another question or stay here long enough to grow to answer for others. – LMSingh Sep 29 '20 at 23:30

1 Answers1

6
select t.acno
      ,t.name
      ,count(Q.trans) as test
      ,Q.transvalue
      ,q.transdate 
from dbo.test t inner join dbo.testaqc q 
on t.acno = Q.acno 
group by t.acno,t.name,Q.transvalue,q.transdate

Any column that is your select statement and not in an aggregate function must come in GROUP BY clause. as it says in the error message you are getting.

M.Ali
  • 67,945
  • 13
  • 101
  • 127