468,257 Members | 1,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,257 developers. It's quick & easy.

Help with group by

The error I get is SQL0206N "TEAM" is not valid in the context where
it is used. SQLSTATE=42703 and the problem lies with the group by team
line. When I remove the group by team statement db2 says that I have a
column function without a group by statement so I need it.
Thank you.

select * \
from (select team1 as team, sum(score1) as goals, count(*) as wins \
from (select team1 as team1, team2 as team2, score1 as
score1, score2 as score2 \
from game g1 \
union all \
select team2 as team1, team1 as team2,
score2 as score1, score1 as score2 \
from game g2 ) as teams \
group by team) as result \
where result.score1 > result.score2

Nov 12 '05 #1
3 6259
You cannot pick up the correlation name from the select list and "carry
it over to the group by.
Simply use the original expression (team1 in this case).

select *
from (select team1 as team,
sum(score1) as goals,
count(*) as wins
from (select team1 as team1,
team2 as team2,
score1 as score1,
score2 as score2
from game g1
union all
select team2 as team1,
team1 as team2,
score2 as score1,
score1 as score2
from game g2 ) as teams
group by team1) as result
where result.score1 > result.score2

Cheers
Serge
Nov 12 '05 #2
Serge,

given that bits of Informix are permeating into DB2, do you think this
will ever change? From using Informix back in 2001, you could write
something like

SELECT char(int(date(booking_timestamp))) || booking_ref ||
right(customer_surname, 10), count(*)
FROM bookings
GROUP BY 1

and the database would happily interpret this. (MySQL's just the same
in this regard).

But in Oracle (8 was my last contact with it), SQL Server (7 & 2000),
postgres & DB2 UDB, you have to write the whole group by clause out
explicitly. And then every time somebody changes the select clause,
they can be guaranteed to forget or mistype something in the
corresponding part of the group by...

While it's unlikely that you'd do anything to carry out the operation
above, there are a lot of times that I've had programmers used to
mySQL complaining that they can't do a GROUP BY 1, 2, etc (and given
that they can ORDER BY 1 it seems a bit inconsistent)

Regards

JCSJF

Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in message news:<fQ**********************@news01.bloor.is.net .cable.rogers.com>...
You cannot pick up the correlation name from the select list and "carry
it over to the group by.
Simply use the original expression (team1 in this case).

select *
from (select team1 as team,
sum(score1) as goals,
count(*) as wins
from (select team1 as team1,
team2 as team2,
score1 as score1,
score2 as score2
from game g1
union all
select team2 as team1,
team1 as team2,
score2 as score1,
score1 as score2
from game g2 ) as teams
group by team1) as result
where result.score1 > result.score2

Cheers
Serge

Nov 12 '05 #3
James,

Blood is thicker than water. So yes: It is thinkable to extend the
support for column numbers from ORDER BY to GROUP BY.
The good thing is that in reality (i.e. non pathological cases) there is
no conflict with the Standard.

Cheers
Serge
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by dmbkiwi | last post: by
9 posts views Thread by netpurpose | last post: by
1 post views Thread by Sarah | last post: by
28 posts views Thread by stu_gots | last post: by
reply views Thread by RJN | last post: by
1 post views Thread by RJN | last post: by
1 post views Thread by Rahul | last post: by
4 posts views Thread by dennis.joseph | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.