# Select query having count and variable

I have a table like the one below.

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:

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.

-
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
Wow. I still do not follow, sorry. – Aaron Bertrand Apr 3 '13 at 12:03
I think it's because he is using 1 as his refid in the parameter. – JNK Apr 3 '13 at 12:36
I'm working on a fiddle for this – JNK Apr 3 '13 at 12:40

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.

-
,Thank you Sooo much. This Is the one I exactly require. – Krishna Thota Apr 3 '13 at 12:53
@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
This fiddle-2 has the same results. – ypercubeᵀᴹ Apr 3 '13 at 13:17
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
``````
-