Help with Query

I have a table xyz with 3 columns. I need a row number per each group

for example if table xyz has following 4 records with 3 columns name, place, zip as shown below. I need my column group_count as shown under group_count column:

Name   place       zip               group_count

ABC      Tampa     33601                  1
ABC      Tampa      33601                 1
LMN      Tampa      33613                 2
PQR      Austin       74751                 3

basically i want to count once for each group of (name,place,zip). Can this be done using analytical functions? Also let me know if there is any other way..

Thanks,
gs79Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

farwaCommented:
if ur groups are fixed u can try this...

select  
Name ,  place ,      zip,  (case(Name)when 'ABC' then 1 when 'LMN' then 2 when 'PQR'  then 3 END) AS             group_count
From xyz
0
farwaCommented:
but if the groups are not known and count is also not fixed then there must be some other way..

PS: the solution might not be efficient but it can be used for hint... Please tell if this helped u or v can find some other solution
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
test this :

select name, place , zip ,
row_number() over ( partition by name, place, zip
order by name , place, zip ) group_count
from your_table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gs79Author Commented:
I tried row_number. It is counting records for each group and resetting as shown below:

ABC      Tampa     33601                  1
ABC      Tampa      33601                 2
LMN      Tampa      33613                 1
PQR      Austin       74751                 1

thanks
0
gs79Author Commented:
My groups are not fixed. It is a huge table. my actual table is different and has large number of records. I used the above example to keep it simple
0
Pratima PharandeCommented:
try this

Select Y.name,Y.place,Y.zip , X.group_count
from
your_table Y
,
(
select name, place , zip ,
row_number() over ( partition by name, place, zip
order by name , place, zip ) group_count
from your_table
group by name, place , zip ) X
Where  X.name = Y.name and X.place=Y.place,X.zip = Y.zip
0
Naveen KumarProduction Manager / Application Support ManagerCommented:
this works for me perfectly. try this :

select y.*
from
( select a.*, row_number() over ( order by name, place, zip ) group_count
from ( select distinct name, place, zip from xyz) a ) y, xyz
where xyz.name || xyz.place || xyz.zip = y.name || y.place || y.zip
order by xyz.name, xyz.place, xyz.zip  
0
devindCommented:
Following query should work
select name,place,zip,dense_rank() over(order by name,place,zip) from xyz
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PilouteCommented:
SQL> create table test
  2  (c1 varchar2(10),
  3  c2 varchar2(10),
  4  c3 number(9));

Table créée.

SQL> insert into test values ('ABC','Tampa',33601);

1 ligne créée.

SQL> insert into test values ('ABC','Tampa',33601);

1 ligne créée.

SQL> insert into test values ('LMN','Tampa',33613);

1 ligne créée.

SQL> insert into test values ('PQR','Austin',74751);

1 ligne créée.

SQL> commit;

Validation effectuée.

SQL> select * from test;

C1         C2                 C3
---------- ---------- ----------
ABC        Tampa           33601
ABC        Tampa           33601
LMN        Tampa           33613
PQR        Austin          74751

SQL> select c1, c2, c3, dense_rank() OVER (order by c1,c2,c3) from test;

C1         C2                 C3 DENSE_RANK()OVER(ORDERBYC1,C2,C3)
---------- ---------- ---------- ---------------------------------
ABC        Tampa           33601                                 1
ABC        Tampa           33601                                 1
LMN        Tampa           33613                                 2
PQR        Austin          74751                                 3


Cheers,
P
0
PilouteCommented:
@ devind.

you're right. haven't seen your answer...

Cheers,
P
0
farwaCommented:
to me devind's answer seems the best.. its efficient easy n simple
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.