| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Group By question
A copy of this was sent to dolans_at_stripe.Colorado.EDU (Sean Dolan)
(if that email address didn't require changing)
On 22 Dec 98 21:17:45 GMT, you wrote:
> I am having a tough time trying to group the results below. Please help if you can, I would greatly appreciate it:
>
> RECORDID COUNT WORDID
>
> 234 1 5678
> 234 1 9807
> 478 1 2345
> 987 1 7642
>
>What I would like is to have 234 show a count of 2 not 1.
> RECORDID COUNT WORDID
>
> 234 2 5678
> 234 2 9807
> 478 1 2345
> 987 1 7642
>
>And to still keep the wordid associated with the record id. I have tried to do a group by, but it tells me that my "WORDID" is not a single-group function???
>I know I am doing a count for a record of ID and WordId (which would naturally return 1), but once it's returned, how do I group the record (or at LEAST the count of the records) into a sum of how many times they were returned?
>
>Happy Holidays - and thank you,
>Sean Dolan
>
>PS please respond to sean_at_3si.com over the Holidays. Thanks again.
In order to COUNT, it needs to know what to count by - in this case, it would be RECORDID and WORDID. Since you want the results of a COUNT BY recordid to be merged with the wordid for each recordid, we can code:
select a.recordid, a.cnt, b.wordid
from ( select recordid, count(*)
from T
group by recordid ) A,
T B
That creates a set of counts by recordid and then joins that back to the original table to join that count to each record again...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 22 1998 - 15:41:15 CST
 |
 |