Home » SQL & PL/SQL » SQL & PL/SQL » How to find how many occurences of every ID in table
How to find how many occurences of every ID in table [message #215995] Wed, 24 January 2007 10:01 Go to next message
gkornacki
Messages: 6
Registered: January 2007
Location: Florida
Junior Member
We have a table that is 3.5 million records and I need to find out how many times each ID occurs in that table. It's probably not a difficult PL/SQL but I'm very rusty and need some help.

I need to find out how many id's are in there 4,5,6 or 7 times.

Any help would be appreciated.

Thanks,

Gene
Re: How to find how many occurences of every ID in table [message #215998 is a reply to message #215995] Wed, 24 January 2007 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
select id, count(*) from big_table group by id;
Re: How to find how many occurences of every ID in table [message #215999 is a reply to message #215995] Wed, 24 January 2007 10:14 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Why do you need PL/SQL for that ? You can just do:

select id, count(*)
from groups
group by id
having count(*) between 4 and 7

Re: How to find how many occurences of every ID in table [message #216002 is a reply to message #215995] Wed, 24 January 2007 10:28 Go to previous messageGo to next message
gkornacki
Messages: 6
Registered: January 2007
Location: Florida
Junior Member
I guess I didn't explain it correctly.

I don't want a list of ID's and the count of that ID. I want a count of how many ID's occur 4,5,6 and 7 times.

So I grab the first ID in the table and scan the whole table counting how many times it's in there. Then I would increment a counter if it happens to be a 4 I would increment cnt4 + 1, if 5 cnt5 + 1 and so on.




Re: How to find how many occurences of every ID in table [message #216004 is a reply to message #215995] Wed, 24 January 2007 10:32 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
select id_counts, count(*)
from
(select id, count(*) as id_counts from groups group by id)
group by id_counts
Re: How to find how many occurences of every ID in table [message #216040 is a reply to message #215995] Wed, 24 January 2007 15:36 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

I'm not sure if this is the most efficient way to do this, but here it is:

select count(distinct id) from big_table bt
where 4 = (select count(*) from big_table bt2
where bt2.id = bt.id)

Do the same code for 5,6 and 7 occurrences.
Re: How to find how many occurences of every ID in table [message #216044 is a reply to message #215995] Wed, 24 January 2007 17:22 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
SQL> select * from foo order by 1;

NAME
------------------------------
BDOBBERPUHL
BDOBBERPUHL
BDOBBERPUHL
BDOBBERPUHL
CSTILLER
CSTILLER
CSTILLER
CSTILLER
DBSNMP
DBSNMP
DCHADHA
GRR
TRR

13 rows selected.

SQL> select cnt,count(*) distinct_values from (
select name,count( *) cnt
from foo
group by name
)
group by cnt ;

CNT DISTINCT_VALUES
---------- ---------------
1 3
2 1
4 2

SQL>


Srini
Re: How to find how many occurences of every ID in table [message #216103 is a reply to message #216044] Thu, 25 January 2007 00:33 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Off topic:
Srini, what kind of an aswer is that? I got used to a more sophisticated solution from you ./fa/1587/0/. I know, it doesn't have anything to do with an actual problem, but here it is: my favourite one ./fa/1578/0/
Re: How to find how many occurences of every ID in table [message #216133 is a reply to message #215995] Thu, 25 January 2007 03:47 Go to previous messageGo to next message
nmuthusa
Messages: 2
Registered: January 2007
Location: India
Junior Member
Hi,

I given a example below. I hope, it will helpful to you.


SQL> desc table_1;
Name
---------------
COL1

column COL1 is number data type.

select count(*) from (select col1 ,count(col1) from table_1 group by col1 having count(col1) between 4 and 7 );


if anything wrong in above code, pls let me know. so that i can correct it in future.

Thanks,
Binan
Re: How to find how many occurences of every ID in table [message #216134 is a reply to message #215995] Thu, 25 January 2007 03:56 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You mean, apart from the fact that it doesn't solve the problem gkornacki posted ?

Quote:
So I grab the first ID in the table and scan the whole table counting how many times it's in there. Then I would increment a counter if it happens to be a 4 I would increment cnt4 + 1, if 5 cnt5 + 1 and so on.


Your query will only return one row, so how are you getting the different counters ?
Re: How to find how many occurences of every ID in table [message #216172 is a reply to message #215995] Thu, 25 January 2007 07:36 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

It looks like srini's solution works, except he forgot about the "between 4 and 7" part. So, to answer the original question, his code should look something like this:

select cnt,count(*) distinct_values from (
select id,count(*) cnt
from big_table
group by id)
group by cnt
having cnt between 4 and 7;
Re: How to find how many occurences of every ID in table [message #216175 is a reply to message #215995] Thu, 25 January 2007 07:44 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
That would be that one that is identical to what I posted about 7 hours earlier. Smile
Re: How to find how many occurences of every ID in table [message #216177 is a reply to message #215995] Thu, 25 January 2007 07:51 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

Yeah, except you didn't have "having id_counts between 4 and 7". Cool
Re: How to find how many occurences of every ID in table [message #216179 is a reply to message #216177] Thu, 25 January 2007 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, he did (in his post on Wed, 24 January 2007 17:14).
Re: How to find how many occurences of every ID in table [message #216180 is a reply to message #216179] Thu, 25 January 2007 07:58 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

But that one doesn't work correctly. Razz
Re: How to find how many occurences of every ID in table [message #216181 is a reply to message #215995] Thu, 25 January 2007 08:03 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I was aware that it was a possible requirement but, having demonstrated the technique in the earlier post, I felt it was not necessary to include it again in the revised solution.
Re: How to find how many occurences of every ID in table [message #216182 is a reply to message #216179] Thu, 25 January 2007 08:05 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Littlefoot wrote on Thu, 25 January 2007 08:55
Yes, he did (in his post on Wed, 24 January 2007 17:14).

Hehehe, of course, in your time zone it shows up as 17:14. In mine it shows up as 11:14. It would make people scratch their heads if in your time zone, there was one posted at 23:14 , which would appear to be 17:14 in my time zone and if it did not have any reference to between 4 and 7. People would be calling you daft for referring to a message that they couldn't find the reference and might start saying things like, "Did you use the see invisible tags, because I don't see what you are talking about?"
Just thought I'd start my day with a little message to get people thinking. I hope it works.
Re: How to find how many occurences of every ID in table [message #216183 is a reply to message #216181] Thu, 25 January 2007 08:08 Go to previous messageGo to next message
Ericle
Messages: 44
Registered: April 2006
Location: United States of America ...
Member

That's okay, I was just being nitpicky. You had it right and no one acknowledged it. Until now. Nice job! Very Happy

Now we just need Gene to tell us that it worked for him.

[Updated on: Thu, 25 January 2007 08:12]

Report message to a moderator

Re: How to find how many occurences of every ID in table [message #216186 is a reply to message #215995] Thu, 25 January 2007 08:18 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Ooops, I should have read Cthulhu's solution more carefully.
Yes, you had the core question solved .

Srini
Re: How to find how many occurences of every ID in table [message #217455 is a reply to message #216002] Fri, 02 February 2007 05:44 Go to previous message
haikasi
Messages: 7
Registered: October 2006
Junior Member
select id from <table-name> group by id having count(*)
between (4,7)
Previous Topic: Please Help to Solve this Query..
Next Topic: Resolved
Goto Forum:
  


Current Time: Fri Dec 02 18:29:16 CST 2016

Total time taken to generate the page: 0.21353 seconds