column not contained in either an aggregate function or the GROUP BY

[stonehead]

Please help me to fix this code.
error: "myView.queue is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


select
a.account,
min(a.time_on) as Informal , a.queue as In_agent,
max (b.time_on) as Formal, b.queue as For_Agent

from dbo.myView a inner join dbo.myView b on a.account = b.account

where len(a.queue)> 2

group by a.account

 

[ESquared]

group by a.account, [red]a.queue, b.queue[/red]

 

[gmmastros]

ESquared,

I'll bet you a purply that adding those columns to the group by will cause the query to work, but also return the wrong data.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

I bet so, too, but sometimes letting people find it out for themselves is more instructive.

 

[gmmastros]

I agree.

What are you thinking here?

A derived table with the account, min and max in it, joined back to itself a couple times to get the queue values?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

Something like that, but I don't know enough about the structure and the desired result to offer anything at this point.

 

[gmmastros]

Here. Maybe this will help: thread183-1422270



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom