Inner Join with Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inner Join with Group By

Author

Topic

Fancy
Starting Member

8 Posts

Posted - 2009-03-03 : 20:53:22

I build a view, it contains inner join and group by clauses.

SELECT     dbo.Entry.Status, dbo.Entry.Deleted, dbo.tblBasliklar.BasligiAcan, dbo.tblBasliklar.BaslikTarihi, dbo.tblBasliklar.Baslik, dbo.tblBasliklar.OkumaSayisi, 
                      dbo.tblBasliklar.KayitSayisi, dbo.tblBasliklar.SonYazan, dbo.tblBasliklar.Bugunkuentry, dbo.Entry.OwnerUID, dbo.tblBasliklar.Deleted AS Deleted2, 
                      dbo.tblBasliklar.Gizle, dbo.Entry.BaslikID
FROM         dbo.Entry INNER JOIN
                      dbo.tblBasliklar ON dbo.Entry.BaslikID = dbo.tblBasliklar.BaslikID
GROUP BY dbo.tblBasliklar.Baslik

But i taking error.

Column 'vw_internentries.BaslikID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

i using SQL 2005 Enterprise.
Please help
Thanks

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-03-03 : 21:14:29

as you have provided very little information about result you desire from your query

In general,

all the columns in the SQL SELECT statement that do not use an aggregate function need to be in the group by clause

Fancy
Starting Member

8 Posts

Posted - 2009-03-03 : 21:16:37

I tried all methods which are i know.
What can i do? can you explain with an example?
thx

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 22:49:32

if u are usign any aggregate function in the select statement then the query must have the group by clause

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 22:50:58

See this example,

declare @temp table ( date datetime,price int )
insert into @temp
select '1/1/2009',10 union all
select '5/1/2009',15 union all
select '6/1/2009',15 union all
select '4/1/2009',15 union all
select '7/1/2009',20 union all
select '10/1/2009',25 union all
select '1/12/2009',25 union all
select '1/19/2009',25 union all
select '2/19/2009',25

select max(Date) as date,price from @temp
group by price

Fancy
Starting Member

8 Posts

Posted - 2009-03-04 : 20:55:08

Thanks for helps,
I tried but i taking an error.
This is my view:

SELECT     dbo.Entry.Status, dbo.Entry.Deleted, dbo.tblBasliklar.BasligiAcan, dbo.tblBasliklar.BaslikTarihi, dbo.tblBasliklar.Baslik, dbo.tblBasliklar.OkumaSayisi, 
                      dbo.tblBasliklar.KayitSayisi, dbo.tblBasliklar.SonYazan, dbo.tblBasliklar.Bugunkuentry, dbo.Entry.OwnerUID, dbo.tblBasliklar.Gizle, 
                      dbo.Entry.BaslikID
FROM         dbo.Entry INNER JOIN
                      dbo.tblBasliklar ON dbo.Entry.BaslikID = dbo.tblBasliklar.BaslikID
GROUP BY dbo.tblBasliklar.Baslik, dbo.Entry.Status, dbo.Entry.Deleted, dbo.tblBasliklar.BasligiAcan, dbo.tblBasliklar.BaslikTarihi, dbo.tblBasliklar.Baslik, 
                      dbo.tblBasliklar.OkumaSayisi, dbo.tblBasliklar.KayitSayisi, dbo.tblBasliklar.SonYazan, dbo.tblBasliklar.Bugunkuentry, dbo.Entry.OwnerUID, 
                      dbo.tblBasliklar.Gizle, dbo.Entry.BaslikID

When Executed:
Possible index corruption detected. Run DBCC CHECKDB.

I runned the dbcc checkdb and checktable, all tables and the db is OK.

Where is the problem,
thanks for your helps

Fancy
Starting Member

8 Posts

Posted - 2009-03-05 : 18:54:55

any help_?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources