Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table like the one below.

Main Table

I need to form a select query. I'm going to pass a RefID like

SELECT SNo  FROM Register WHERE RefID = 1

And beside the SNo in the Statement, I need to have another column which contains the total COUNT of refID belonging to that SNo. For example:

Required Output

The Precise Question as @JNK Specified:

Show me all the Sno values with a RefID of 1, and for each Sno show me also a count of how many times that Sno value is itself used as a RefID anywhere else in the table.

share|improve this question
    
Why are any of these zero? I think you need to explain your logic a little better. –  Aaron Bertrand Apr 3 '13 at 12:00
    
Because the SNo 5 is not there in RefID column. and Since Sno 2 is referenced 1 time in RefID, the Cnt of Sno 2 in Output is 1. –  Krishna Thota Apr 3 '13 at 12:02
1  
Wow. I still do not follow, sorry. –  Aaron Bertrand Apr 3 '13 at 12:03
1  
I think it's because he is using 1 as his refid in the parameter. –  JNK Apr 3 '13 at 12:36
1  
I'm working on a fiddle for this –  JNK Apr 3 '13 at 12:40

2 Answers 2

up vote 3 down vote accepted

OK, this should get it for you. I believe what you are saying is:

Show me all the Sno values with a RefID of 1, and for each Sno show me also a count of how many times that Sno value is itself used as a RefID anywhere else in the table.

I'm sure this can be tweaked but it gives what you are after.

SELECT
  SNo,
  COALESCE(MAX(sub.CT), 0)
FROM
  dbo.MyTable T
LEFT JOIN
  (SELECT RefID, COUNT(*) as CT
   FROM dbo.MyTable
   GROUP BY RefID) Sub
    ON Sub.RefID = T.Sno 
WHERE
  T.RefId = 1
GROUP BY Sno

There's a fiddle here with an example.

share|improve this answer
    
,Thank you Sooo much. This Is the one I exactly require. –  Krishna Thota Apr 3 '13 at 12:53
2  
@KrishnaThota happy to help. For your next question, make sure you fully explain the relationships in the table. You didn't make it clear that RefID was a self-reference to the Sno in the same table, which made this very hard to figure out –  JNK Apr 3 '13 at 12:55
    
I'm sorry. Thank you I'll keep that in mind. –  Krishna Thota Apr 3 '13 at 12:56
1  
This fiddle-2 has the same results. –  ypercube Apr 3 '13 at 13:17
1  
I overcomplicated! I originally tried a CROSS APPLY but should stuck with a good old LEFT JOIN –  JNK Apr 3 '13 at 13:20

Why not simply use an inline subquery ?

select A.SNo,
       (select count(*) from Register B where B.Sno = A.Sno) as Cnt
from Register A
where A.RefId = 1
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.