Re: Problem counting records

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

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sir Hystrix" <SirHystrix@xxxxxxxxxxxx> wrote in message news:474fe374$0$22307$ba620e4c@xxxxxxxxxxxxxxxxx
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.

.

Relevant Pages

  • Problem counting records
    ... I am struggling with a simple query, but I just don't see it. ... The table holds new messages as well as replies to messages. ... Any assistance would be greatly appreciated. ...
    (comp.databases.ms-sqlserver)
  • Re: Multiple combo boxes - send parameters to a query?
    ... >Thank you for your replies. ... >I am eternally grateful to everyone on this forum who has helped me. ... and this generated some sort of infinitely looping query. ... >Forms!yourform.cboQueriedCustomer Is Null to each criteria the SQL view just ...
    (microsoft.public.access.queries)
  • Re: Operating System Scan
    ... WMI functions built into Windows 2000 and above to query all sorts of ... * PLEASE post all messages and replies in the newsgroups ...
    (microsoft.public.win2000.networking)
  • Re: Dlookup Help
    ... I am try to use the Dlookup to use a value that I get in the report ... to lookup in a query and return me back some text. ... If you don't understand the replies, ...
    (microsoft.public.access.reports)
  • Device not recognized (OT)
    ... I originally posted this query in the alt.windows98 group, ... any replies. ... I'm hoping that mebbe K-Man or relic can help me here, ... This was sent to me by a friend about his Win 98 computer: ...
    (alt.os.windows-xp)