Home » SQL & PL/SQL » SQL & PL/SQL » How To reterive the record counts (MS SQL 2008,,WINXP)
How To reterive the record counts [message #396541] Tue, 07 April 2009 03:05 Go to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
Hi,

Can any one help me out to get the record count. The record count here i mean like, Consider the below table format,

Table 1

Column_ID_A ----Column_Pruchase_B.....
1 ---------------------XYZ
2 ---------------------ADS
1 ---------------------ASD
1 ---------------------SED
3 ---------------------ERD
3 ---------------------EFG
4 ---------------------GHS
5 ---------------------FRS
4 ---------------------ERF
4 ---------------------TRT
6 ---------------------DRG
5 ---------------------IDE
7 ---------------------FRS


Here i need to take a count to show how many ID's are repeated how many times. for the above Example the o/p should be like,

Table 2:

Record_Count----Count_Value
1 --------------------- 3
2 --------------------- 2
3 --------------------- 2


So here 3 ID's comes only once in the table (2,6 & 7)
2 ID's come twice in the table (3 & 5)
2 Id's come thrice (1 & 4)

So i need output in the second table format.
Can anyone please help me out....
Re: How To reterive the record counts [message #396542 is a reply to message #396541] Tue, 07 April 2009 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to first count the number of rows per id in an inline subquery and then count the number of rows per the first count in an outer query.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, always post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: How To reterive the record counts [message #396544 is a reply to message #396541] Tue, 07 April 2009 03:13 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
with data as (select level lv, sysdate dt from dual connect by level <= 2
union all
select level lv, sysdate dt from dual connect by level <= 3)

select lv, count(lv) from data group by lv;
Re: How To reterive the record counts [message #396560 is a reply to message #396544] Tue, 07 April 2009 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question is not the number of rows per lv but the number of rows per number of rows per lv.

Regards
Michel
Re: How To reterive the record counts [message #396566 is a reply to message #396560] Tue, 07 April 2009 04:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know whether information posted in topic title is correct:
Quote:
How To reterive the record counts (MS SQL 2008, WINXP)
but - this might be the wrong Forum.
Re: How To reterive the record counts [message #396573 is a reply to message #396566] Tue, 07 April 2009 04:27 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
Friends,

Sorry Sad its Microsoft SQL 2005...

Can i get query for it pls...?

Re: How To reterive the record counts [message #396575 is a reply to message #396573] Tue, 07 April 2009 04:30 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
ramsk0408 wrote on Tue, 07 April 2009 11:27
Friends,

Sorry Sad its Microsoft SQL 2005...

Can i get query for it pls...?




This is an Oracle forum ... are you sure you want help from the competitor Razz

But the solution has been given, not in a ready-to-use-query-form but a hint on how to tackle your problem:

Michel Cadot

You have to first count the number of rows per id in an inline subquery and then count the number of rows per the first count in an outer query.


Re: How To reterive the record counts [message #396576 is a reply to message #396573] Tue, 07 April 2009 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should work on Sql Server, and give you what you asked for, unlike the previous query.
create table test_172 (col_1  number);

insert into test_172 values (1);
insert into test_172 values (1);
insert into test_172 values (1);
insert into test_172 values (2);
insert into test_172 values (2);
insert into test_172 values (3);
insert into test_172 values (4);
insert into test_172 values (5);

select cnt  
      ,count(*)
from  (select col_1
      ,count(*) cnt 
       from test_172
       group by col_1)
group by cnt;
Re: How To reterive the record counts [message #396579 is a reply to message #396576] Tue, 07 April 2009 04:43 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
This can be done when we know the No of times an ID can repeat. If we don't know exactly maximum how many times a Id is repeated how this can be accomplised....?
Re: How To reterive the record counts [message #396581 is a reply to message #396575] Tue, 07 April 2009 04:46 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
As i was just looking for some resultfor this query i got this forum link through Google. As it is specified as SQL & PL/SQL, i've raised ma clarification here Smile

Anyway can i have some more details on the Hint given pls......
Re: How To reterive the record counts [message #396607 is a reply to message #396581] Tue, 07 April 2009 05:50 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave you the clue, JRowbottom gave you the query.
What do you want more?

Regards
Michel
Previous Topic: Join and select help
Next Topic: Dynamic Connection in SQLPLUS
Goto Forum:
  


Current Time: Sat Dec 03 16:10:55 CST 2016

Total time taken to generate the page: 0.17913 seconds