Grouping By problem - 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)

Grouping By problem

Author

Topic

jaslar18
Starting Member

2 Posts

Posted - 2008-06-24 : 12:58:55

Hi,

So far, here's what i have as an SQL statement. I absolutely need to return the LetterID, Description, UserID and the Date. The PrintGroupID isn't necessary, but i included it to make it easier to understand the query.

quote:

1 select Letter.LetterID, LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate, PrintGroupID
2 from PrintQueue
3 inner join Letter
4 on PrintQueue.LetterID = Letter.LetterID
5 inner join LetterType
6 on Letter.LetterTypeID = LetterType.LetterTypeID

The results returned are:

As you can see from the results, some rows have the same PrintGroupID. What I would like to do is group the results by PrintGroupID and have a Count of how many rows have the same PrintGroupID.

The problem is that i keep getting :Column 'xxxxxxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. So, i understand that this means i need to include all the columns in my select statement in my GROUPBY clause, but then it doesn't do the grouping at all. The PrintGroupID is located in the Letters table.

Can anyone help me figure this out?

Thank you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 13:01:15

Did you try this?

1 select Letter.LetterID, LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate, count(PrintGroupID)
2 from PrintQueue
3 inner join Letter
4 on PrintQueue.LetterID = Letter.LetterID
5 inner join LetterType
6 on Letter.LetterTypeID = LetterType.LetterTypeID
7 group by Letter.LetterID, LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate

Madhivanan

Failing to plan is Planning to fail

jaslar18
Starting Member

2 Posts

Posted - 2008-06-24 : 14:14:57

quote:
Originally posted by madhivanan

Did you try this?

1 select Letter.LetterID, LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate, count(PrintGroupID)
2 from PrintQueue
3 inner join Letter
4 on PrintQueue.LetterID = Letter.LetterID
5 inner join LetterType
6 on Letter.LetterTypeID = LetterType.LetterTypeID
7 group by Letter.LetterID, LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate

Madhivanan

Failing to plan is Planning to fail

I'll only be able to try this tomorrow, but wouldn't that Count each PrintGroupID for each grouped Item? Since the LetterIDs do not have any duplicates, the results would remain the same and the Count would always be 1?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-24 : 14:21:32

Yes it is. Then try

Select t1.LetterID,t2.Description, t2.UserIDCreate, t2.DateTimeUpdate,t2.counting from
Letter t1 inner join
(
	select  LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate, count(PrintGroupID) as counting
	from PrintQueue
	inner join Letter
	on PrintQueue.LetterID = Letter.LetterID
	inner join LetterType
	on Letter.LetterTypeID = LetterType.LetterTypeID 
	group by LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate
) as t2
on t1.Description=t2.Description and t1.UserIDCreate=t2.UserIDCreate and t1.DateTimeUpdate=t2.DateTimeUpdate

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-25 : 01:34:48

quote:

Originally posted by madhivanan

Yes it is. Then try

Select t1.LetterID,t2.Description, t2.UserIDCreate, t1.DateTimeUpdate,t2.counting from
Letter t1 inner join
(
	select  LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate, count(PrintGroupID) as counting
	from PrintQueue
	inner join Letter
	on PrintQueue.LetterID = Letter.LetterID
	inner join LetterType
	on Letter.LetterTypeID = LetterType.LetterTypeID 
	group by LetterType.Description, Letter.UserIDCreate, Letter.DateTimeUpdate
) as t2
on t1.Description=t2.Description and t1.UserIDCreate=t2.UserIDCreate and t1.DateTimeUpdate=t2.DateTimeUpdate

Madhivanan

Failing to plan is Planning to fail

I think DateTimeUpdate field should be removed from grouping condition as it will they have time part with unique values.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-25 : 09:49:21

Yes. Or you can omit it by dateadd(day,datediff(day,0,Letter.DateTimeUpdate),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources