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

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.......
XxtremeProAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
JacobfwCommented:
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
 
XxtremeProAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
XxtremeProAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
XxtremeProAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
Wow.  That is a really cool trick.  I'll have to remember that one.
0
 
slightwv (䄆 Netminder) Commented:
It is, isn't it.  I loved it!  It was immediately added to my EE knowledgebase.
0
 
XxtremeProAuthor Commented:
thank you. solved the problem
0
 
slightwv (䄆 Netminder) Commented:
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
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.

All Courses

From novice to tech pro — start learning today.