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)

Group By

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-10-30 : 08:00:17

Having a bit of trouble getting this statement to work the way I need it.

I need it to group by location then order by dates...

Select ev.EventID, ev.StartDate, ev.EndDate, ev.ActivityType, el.LocationName, el.LocationName2, el.URL
from Event ev
INNER JOIN EventLocation el on ev.EventLocationId = el.EventLocationId
Where ev.Eventcode = @code
GROUP BY el.LocationName
Order by ev.StartDate

And getting this error:
Column 'Event.EventId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any suggestions are appreciated.

Zath

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:05:07

you cant use other fields directly in select if you group by particular field. you need to apply aggregate functions over others like MIN(),MAX() etc. you can give some sample data and output to illustrate what you want in which we can try rewriting query.

shm
Yak Posting Veteran

86 Posts

Posted - 2008-10-30 : 08:12:24

u shld take all the col's in the group by clause wt u had taken in the select

i think u can try this:

Select ev.EventID, ev.StartDate, ev.EndDate, ev.ActivityType, el.LocationName, el.LocationName2, el.URL
from Event ev
INNER JOIN EventLocation el on ev.EventLocationId = el.EventLocationId
Where ev.Eventcode = @code
GROUP BY el.LocationName,ev.EventID,ev.StartDate, ev.EndDate,ev.ActivityType,el.LocationName2,el.URL
Order by ev.StartDate

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-10-30 : 08:12:43

Sure, here's what I need it to look like:

883 2006-06-19 2006-06-20 SM Atlanta
991 2007-01-29 2007-01-30 SM Atlanta
1004 2007-11-08 2007-11-09 SM Atlanta
1186 2009-11-09 2009-11-10 SM Atlanta
1283 2009-01-12 2009-01-13 SM Va Tech
1295 2009-03-10 2009-03-13 SM Va Tech

For better reading purposes, I removed the url field and others.
The above data is grouped by locations then by start dates (the first date column).

Thanks,

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-10-30 : 08:15:02

quote:
Originally posted by shm

u shld take all the col's in the group by clause wt u had taken in the select

i think u can try this:

Select ev.EventID, ev.StartDate, ev.EndDate, ev.ActivityType, el.LocationName, el.LocationName2, el.URL
from Event ev
INNER JOIN EventLocation el on ev.EventLocationId = el.EventLocationId
Where ev.Eventcode = @code
GROUP BY el.LocationName,ev.EventID,ev.StartDate, ev.EndDate,ev.ActivityType,el.LocationName2,el.URL
Order by ev.StartDate

Actually I have tried that and it doesn't group the way I need.

Zath

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-30 : 08:18:24

quote:
Originally posted by Zath

Sure, here's what I need it to look like:

883 2006-06-19 2006-06-20 SM Atlanta
991 2007-01-29 2007-01-30 SM Atlanta
1004 2007-11-08 2007-11-09 SM Atlanta
1186 2009-11-09 2009-11-10 SM Atlanta
1283 2009-01-12 2009-01-13 SM Va Tech
1295 2009-03-10 2009-03-13 SM Va Tech

For better reading purposes, I removed the url field and others.
The above data is grouped by locations then by start dates (the first date column).

Thanks,

Zath

Please show us in relation to above result what is the result without grouping.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-10-30 : 08:28:41

Here are the results when not grouped but ordered by startdate:

883 2006-06-19 2006-06-20 SM Atlanta
991 2007-01-29 2007-01-30 SM Atlanta
1004 2007-11-08 2007-11-09 SM Atlanta
1057 2008-10-27 2008-10-28 SM Atlanta
1283 2009-01-12 2009-01-13 SM Va Tech
1295 2009-03-10 2009-03-13 SM Va Tech
1186 2009-11-09 2009-11-10 SM Atlanta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:32:55

not sure what fields you want to group by. i see only single row 1057 missing from output

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2008-10-30 : 08:43:31

I removed a few from the previous data for a better read.

Need to group by el.LocationName
and then order by ev.StartDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 08:57:30

for other fields what do you want to return? their first value or last value? also your sample data contains no duplicate records with same location name and startdate so there would be no change in output.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-10-30 : 10:20:22

[code]ORDER BY el.LocationName, ev.StartDate[/code]would give the desired output?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 12:26:08