Home » SQL & PL/SQL » SQL & PL/SQL » group by purgatory
group by purgatory [message #18486] Fri, 01 February 2002 10:27 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: does varchar2 actually take space
Next Topic: Average a Date?
Goto Forum:
  


Current Time: Thu Jul 02 11:00:14 CDT 2020