How to count

Hello all!
What is a neat way to count the number of children for every parent in
code
below?

CREATE TABLE parent
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

parent_name VARCHAR(100)
)
GO

CREATE TABLE children
(
parent_row_id INTEGER,
child_name VARCHAR(100)
)

INSERT INTO parent (parent_name) VALUES ('King')
INSERT INTO parent (parent_name) VALUES ('Farmer')
INSERT INTO children (child_name, parent_row_id) VALUES ('Prince', 1)Select parent_name, SubQuery.Sum_of_children from parent
INNER JOIN
(
Select parent_row_id,COUNT(*) from children group by
parent_row_id
) SubQuery
ON SubQuery.parent_row_id = parent.row_id

HTH, Jens Suessmeyer.|||SELECT parent_name,
(SELECT COUNT(parent_row_id)
FROM children
WHERE parent_row_id = row_id) AS number_of_children
FROM parent

Good luck,
Tony Sebion

"jonsjostedt@.hotmail.com" <jonsjostedt@.hotmail.com> wrote in message
news:1126624285.607326.195420@.g44g2000cwa.googlegr oups.com:

> Hello all!
> What is a neat way to count the number of children for every parent in
> code
> below?
> CREATE TABLE parent
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> parent_name VARCHAR(100)
> )
> GO
> CREATE TABLE children
> (
> parent_row_id INTEGER,
> child_name VARCHAR(100)
> )
> INSERT INTO parent (parent_name) VALUES ('King')
> INSERT INTO parent (parent_name) VALUES ('Farmer')
> INSERT INTO children (child_name, parent_row_id) VALUES ('Prince', 1)