How to join columns in SQL Server?

Question

I have 2 tables

Table 1:

create table Tb1
(
    id int identity(1,1) primary key,
    repoted int foreign key references Tb2(id)
)
go

Table 2

create table Tb2
(
    id int identity(1,1) primary key,
    name nvarchar(100)
)
go

Procedure:

create procedure test
@reported int
as
    select 
       reported, name, count(reported) as numberofreport 
    from 
       Tb1 
    cross join 
       Tb2 
    where 
       reported = @reported 
    group by 
       reported

When I execute the query, it returns an error:

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

Then I enter edit the group by to reported, name. The error gone but it shows all of my records in the table, only change the name.

Any solution for this problem?

"Column 'Restaurant.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." - that's a very explicit error message. — Mitch Wheat, Apr 20 '15 at 03:03
maybe you mean `INNER JOIN` eg `tb2 INNER JOIN tb1 ON tb2.id = tb1.repoted` — John Woo, Apr 20 '15 at 03:04
Remove the `group by`. Then rephrase the question/title knowing how this changes the observed issue. — user2864740, Apr 20 '15 at 03:17
possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) — Tanner, Apr 29 '15 at 13:34

score 0 · Answer 1 · answered Apr 20 '15 at 03:23

If you are using groupby you need to mention all the columns present in select statement in groupby. You can have any of below query :-

create procedure test
@reported int 
as
select reported,name,count(reported) as numberofreport from Tb1 cross join        Tb2 where reported = @reported group by reported,name

or

select reported,count(reported) as numberofreport from Tb1 cross join        Tb2 where reported = @reported group by reported

Reason behind this rule is , in case of group by, query suppose to return one row for each group (mentioned in group by clause), so if you have two columns in select then it may have more records. Consider scenario you have two rows for one reported value with different name, how database will decide which to return. It may be one to one mapping in your scenario between reported and name, but rules are generic for everyone to avoid runtime failures.

How to join columns in SQL Server?

1 Answers1