Problem counting records

Hi,

I am struggling with a simple query, but I just don't see it.
I have the following example table.

Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0

The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.

What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.

ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0

Any assistance would be greatly appreciated.Did you think of this:

select t1.ID, t1.Subject, count(1) as Num_replies_to
from tbl t1
left join tbl t2
on t2.Reply_to=t1.ID
where t1.Reply_to=0
group by t1.ID, t1.Subject

Bye, Manfred|||What I would like to have returned is this: a list of messages where

Quote:

Originally Posted by

Reply_to = 0 and the number of replies to this message.

A subquery like the example below is one method.

SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sir Hystrix" <SirHystrix@.netscape.netwrote in message
news:474fe374$0$22307$ba620e4c@.news.skynet.be...

Quote:

Originally Posted by

Hi,
>
I am struggling with a simple query, but I just don't see it.
I have the following example table.
>
Table Messages
ID Subject Reply_to
1 A 0
2 Ax 1
3 A 1
4 B 0
5 By 4
6 C 0
>
The table holds new messages as well as replies to messages.
Messages with Reply_to = 0 are top messages, the other messages are
replies to a top message. The subject of a reply message does not
necessarily have to be the same as the subject of the top message.
>
What I would like to have returned is this: a list of messages where
Reply_to = 0 and the number of replies to this message.
>
ID Subject Num_replies_to
1 A 2
4 B 1
6 C 0
>
Any assistance would be greatly appreciated.

|||Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What I would like to have returned is this: a list of messages where
>Reply_to = 0 and the number of replies to this message.

>
A subquery like the example below is one method.
>
SELECT
m.ID,
m.Subject,
(SELECT COUNT(*)
FROM dbo.Messages
WHERE Reply_to = m.ID
) AS Num_replies_to
FROM dbo.Messages AS m
WHERE Reply_to = 0
>

I knew it was simple. It had to be simple. I just didn't see it.
Many thanks to both Dan and Manfred.

Cheers.