| group by purgatory [message #18486] |
Fri, 01 February 2002 10:27  |
drewmoo
Messages: 6
Registered: February 2002
|
Junior Member |
|
|
can someone help me with the proper syntax for the group by clause in the following SQL.
select a.column1 as Stuff,
(select min(b.column2)
from tableb b
where b.column1 = a.column1) as MoreStuff,
count(a.column2) as TotalStuff
from tablea a
more specifically, how do i represent the subquery in the group by clause. I'm new to Oracle (spent 7 years with Informix). In Informix you can do everything positionally, so I could just write
group by 1, 2
Oracle apparently doesn't like that (too simple perhaps). C'mon you hardcore Oracle devotees ... convert me.
|
|
|
|
| Re: group by purgatory [message #18492 is a reply to message #18486] |
Fri, 01 February 2002 10:53  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Your group by function (MIN) is inside the inline query. Therefore that is where your group by clause has to be. If INFORMIX works the way you say, I don't know how it would interpret group by 1, 2.
select a.column1 stuff,
(select min(b.column2)
from table2 b
where a.column1 = b.column1
group by a.column1) MoreStuff,
(select count(a.column2) from table1) TotalStuff
from table1 a
/
|
|
|
|
| Re: group by purgatory [message #18496 is a reply to message #18486] |
Fri, 01 February 2002 11:38 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Is this what you're looking for?
select a.column1 Stuff,
(select min(b.column2)
from table2 b
where b.column1 = a.column1) MoreStuff,
(select count(column2) from table1 c where a.column1 = c.column1) TotalStuff
from table1 a
If not, can you give an example of the data and what you would expect for results?
|
|
|
|
| Re: group by purgatory [message #18498 is a reply to message #18486] |
Fri, 01 February 2002 11:46 |
drewmoo
Messages: 6
Registered: February 2002
|
Junior Member |
|
|
Not exactly what I was looking for. I'd post the query in question out here, but don't want to violate any confidentiality/proprietary B.S. we have here.
Here's the crux of my problem.
I have a complex SQL that has multiple correlated queries in the select clause. The last column in my select clause is an aggregate count(). because of this aggregate, I have to use a group by clause in my main SQL. I know how to use group by if the column selected is just a single column name ... or even if it is a concatenation of 2 or more column names ... or even if i'm using the nvl() or initcap() function on a column name. what I don't know how to do is represent a column in a group by if that column is actually a correlated query.
am i making sense here? i sort of feel like i'm confusing the issue some.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
