By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

Help with group by

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.