Solved

How to know if one field of the database table has many associated records

Posted on 2011-09-21
12
256 Views
Last Modified: 2012-05-12
I have a table T which has rows A,B,C,D

Records shown under A have records associated against it under B

How do I determine if a record under A has more than one unique value associated with it under B..

Remember the Table has got more than 70K+ Records

Would like to see the output some thing like

A          B              Count
---       -----           ----------
1           12              4
             13
             14
             15
2            0               1
3            23              3
              24
              25

I hope the question is understood and also the expected output

Please help experts need a solution at the earliest.......
0
Comment
Question by:XxtremePro
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36576742
This seems like a simple question, but in order to understand more and ensure a correct answer, I will require a table def  (I am assuming you mean COLUMNS (A,B,C,D)?  Are there infact two tables or one table?  what is the key that associates A and B together?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 500 total points
ID: 36576856
Seems like too simple an answer, but is this what you are looking for?

select a, b, count(1)
from t
group by a, b
having count(1) > 1;
0
 

Author Comment

by:XxtremePro
ID: 36577133
Thanks Jacobfw for trying to understand what I have been asking

Really like that question

Thanks johnsone the query worked but more correctly when I removed the last line
but still not quite the answer I wished to see

Answering Jacobfw, I will require a table def
 (I am assuming you mean COLUMNS (A,B,C,D)?  Are there infact two tables or one table?  what is the key that associates A and B together?
Yes Coloumn's A, B ,C, D

Only one Table

Key that associates A and B together ... Not sure as I have not created the table.. but both are primary key with an other field C as primary key

Hope I was clear

0
 

Author Comment

by:XxtremePro
ID: 36577170
select A, B, count(2)
from T
group by A, B

got some output not sure about the results

Please help me with the explanation as I am not sure I interpret the results properly

0
 
LVL 34

Expert Comment

by:johnsone
ID: 36577361
The having was so that you would only see records where there was more than one.  Do you want to see records where there are only 1?  Then what you have posted should give you the count you are looking for.

If these are not correct, can you post some sample data and your expected output?
0
 

Author Comment

by:XxtremePro
ID: 36577395
I was expecting output in this way

A( 1st coloumn )                               B( 2nd Column)                         Count of B ( Showing A has got 2 or 3 or how many its got)
--------------------                               -------------------                            -------------------------------------------------------------------------
xyz                                                            1                                                                          4 ( 4 Records are shown against xyz)
                                                                 2
                                                                 3
                                                                 4
<now the next record with the list of records showing against the record in Column A 1st Column)>

I was thinking I have shown the same sort of output when I initially posted
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:johnsone
ID: 36580523
This should do what you are looking for:

select s.a, t.b, s.cnt
from t, (select a, count(1) cnt from t group by a) s
where s.a=t.a order by s.a, t.b;

It will repeat the first and third columns, but I believe it gives you the results you are looking for.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36580668
Try this little trick posted by another Expert from:
http://www.experts-exchange.com/Database/Oracle/Q_27156351.html


drop table tab1 purge;
create table tab1(a number, b number);
insert into tab1 values(1,12);
insert into tab1 values(1,13);
insert into tab1 values(1,14);
insert into tab1 values(1,15);
insert into tab1 values(2,0);
insert into tab1 values(3,23);
insert into tab1 values(3,24);
insert into tab1 values(3,25);
commit;

--http://www.experts-exchange.com/Database/Oracle/Q_27156351.html

select decode(myrownum,1,a) a,
		b,
		decode(myrownum,1,mycount) mycount
from
(
select a, b, count(1) over(partition by a) mycount, row_number() over(partition by a order by a, b) myrownum
from tab1
)
/

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
ID: 36581219
Wow.  That is a really cool trick.  I'll have to remember that one.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36581256
It is, isn't it.  I loved it!  It was immediately added to my EE knowledgebase.
0
 

Author Closing Comment

by:XxtremePro
ID: 37135637
thank you. solved the problem
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37135660
Can I ask why you selected the answer you did after you posted "but more correctly when I removed the last line
but still not quite the answer I wished to see"
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query returning everything 11 91
Oracle RAC 12c 8 60
ORA-12560: TNS:protocol adapter error 8 87
Oracle Public Synonyms and Privileges 2 51
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now