Toad World® Forums

Include Subquery in group by clause


#1

I seem to always be forgetting how to include a subquery select as part of my group by statement when I’m doing some sql that requires this.

Example of what I mean:

select tbraccd_pidm, sum(tbraccd_balance), tbraccd_term_code
from tbraccd
where tbraccd_term_code in (‘201001’,‘200909’)
group by tbraccd_pidm, tbraccd_term_code

What I want to do is a subquery select from a phone number, like this, but don’t know how to include that in my group by clause. This would be the subquery select I’m using:

(SELECT (SPRTELE_PHONE_AREA || ‘-’ || SPRTELE_PHONE_NUMBER)
FROM sprtele
WHERE sprtele_pidm = spriden_pidm
AND SPRTELE_STATUS_IND IS NULL
AND ROWNUM

Any help would be greatly appreciated.


#2

Peter,

Any clause in the group by must also appear in the select columns output.

select tbraccd_pidm,

sum (tbraccd_balance),

tbraccd_term_code,

(select (sprtele_phone_area || ‘-’ || sprtele_phone_number)

from sprtele

where sprtele_pidm = spriden_pidm

and sprtele_status_ind is null

and rownum

phone

from tbraccd

where tbraccd_term_code in (‘201001’, ‘200909’)

group by tbraccd_pidm,

tbraccd_term_code,

(select (sprtele_phone_area || ‘-’ || sprtele_phone_number)

from sprtele

where sprtele_pidm = spriden_pidm

and sprtele_status_ind is null

and rownum

Though this answers the direct question, let us know if it answers the business
question. This will result in a Cartesian join.

HTH

chris


#3

Nope. Subquery expressions not allowed here.


#4

In my LIMITED experience I’ve found that you cannot use a subquery in a
group by clause. But don’t give up!

A while back someone on this list told us about the “with clause”. A
Quest employee I believe. Sorry I don’t recall the name to give proper
credit. L As search in the archives on ‘with with’ might find it.

Anyway, try something like this:

with white_pages as

(SELECT

sprtele_pidm ,

( SPRTELE_PHONE_AREA || ‘-’ || SPRTELE_PHONE_NUMBER ) as Phone

FROM

sprtele

WHERE

SPRTELE_STATUS_IND IS NULL

– AND ROWNUM

)

select

tbraccd_pidm ,

sum( tbraccd_balance ),

tbraccd_term_code ,

Phone

from

tbraccd ,

white_pages

where

– tbraccd_term_code in (‘201001’,‘200909’) and

tbraccd_pidm = white_pages . sprtele_pidm

group by

tbraccd_pidm ,

tbraccd_term_code ,

Phone

I had to comment a couple of lines to pull data from our DB, but you get the
picture. You may want to select more columns from sprtele, so you can tell what
kind of phones you’re looking at. If you’re wanting names, too,
don’t forget about f_format_name.

HTH,

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg


#5

If you can break it out of being a subquery, and put it in the main
Select, it’s easy:

select tbraccd_pidm
, tbraccd_term_code
, sprtele_grouped.phone
, sum(tbraccd_balance)
from tbraccd
, (SELECT MAX(SPRTELE_PHONE_AREA || ‘-’ || SPRTELE_PHONE_NUMBER)
Phone
– use MAX to pick a random one if there is more than one
– for a given sprtele_pidm
, sprtele_pidm
FROM sprtele
WHERE SPRTELE_STATUS_IND IS NULL
GROUP BY sprtele_pidm) sprtele_grouped
where tbraccd_term_code in (‘201001’,‘200909’)
and sprtele_grouped.sprtele_pidm (+) = tbraccd.spriden_pidm
group by tbraccd_pidm, tbraccd_term_code, sprtele_grouped.phone

Nate Schroeder
IT Commercial Technical Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167
314-694-2592


#6

I’ll give that a try. Guess there’s many ways to skin a cat… and I’m always learning. Thanks.


#7

According to the robot in Lost in Space, when Doctor Smith says there’s
more than one way to skin a cat – robot says exactly 32 ways J


#8

Who would ever want to skin a cat anyway?

Tobi


#9

Is there a way to convert the erx file to a more universally utilized format? I
want to be able to distribute the database model but am afraid some of the
people I am sending to might not have a modeling program specifically.

Thanks,
Eva


#10

An interesting article on the phrase


#11

Afternoon,

Who would ever want to skin a cat anyway?
Me! But only dead ones.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#12

you will want to consider subquery cardinality before incorporating your
subqueries into the main query specifically:
if the resultset from your subquery cardinality is low then leave it be
if the resultset from your subquery cardinality is high then incorporate
subquery into main body

GROUP BY should be implemented sparingly and only as a precursor for accumulator
functions such as AVG and SUM
roger/norm/anyone?
Martin


Verzicht und Vertraulichkeitanmerkung

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.


#13

Hi Martin,

GROUP BY should be implemented sparingly and only as a
precursor for accumulator functions such as AVG and SUM
roger/norm/anyone?

It’s not a recommendation I’ve ever seen. Aggregate functions are fine -
if you need the answers that they give. There is no need to avoid them
(like GOTO in programming) and reinvent the wheel (much beloved of Java
developers ). I’m pretty sure that Oracle’s implementation of
these aggregate functions is a lot more efficient that whatever we could
write ourselves to implement them.

GROUP BY is fine.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#14

You mean like HTML or PDF? The ER Diagram window already supports that.

You just have to hit the report button in the toolbar.


#15

Here we go on the differences of definition…

use MAX to pick a random one if there is more than one

Err… max doesn’t provide randomness… not even a bad facsimile
of it. For example, if the phone numbers selected for a given pid are:

999-5551234

999-5559876

999-5554321

You will always, always, always get 999-5559876 using the max function…
unless of course someone changes the dataset. Random implies there’s some
chance that one of the other two numbers will be picked and that’s simply
not true with the max function.

Roger S.


#16

It is possible that when the person said RANDOM he did not mean the mathematical
word random but maybe something in the line of “any old number” and
if all you want is some number then both MAX and MIN will give you “some
number”. Of course Max always gives the biggest and Min always the
smallest.
image001.jpeg


#17

I’m not really following this thread, so maybe it doesn’t apply
here, but you can get rows in a random order with this:

order by dbms_random .value


#18

Jeff,

There are just so many buttons! I found the report button and it did even more
than I could have asked. That is a VERY cool thing. Thanks.

Eva


#19

and it did even more than I could have asked

You’re not asking for enough!

Apart from Unicode support, the ER Diagram upgrade seems to be one of the more
popular new features for Toad 10. It’s brand new, so don’t feel too bad
for not knowing about it.

http://www.toadworld.com/BLOGS/tabid/67/EntryID/371/Default.aspx


#20

Yep, that’s pretty much what I meant by “random” –
“I don’t care which one is chosen.” Indeed, it will be
deterministic, but I don’t care.

What word other than Random would have communicated that better?

Nate Schroeder

IT Commercial Technical Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. G3WI - Saint Louis, MO - 63167

314-694-2592