Help with simple query 1

[bostonkiwi]

Hi,

I am new to MS SQL Server 2000 and have a really simple query I need help with.

Right now I have the following query:

SELECT *
FROM dbo.scores
WHERE (testKey LIKE 'EN_PS1%')

This of course returns all the rows in the table where the testkey is begins with EN_PS1

However, what I need is to count how many entries there in the table that match the specified 'testkey' for each of the entries in a column/field called parentkey.

For example, if there were 10 records in the parentkey column that matched EN_PS1% I would get results saying:

parentkeyxxx 10

Thanks,

Chris.

 

[pweegar]

try this:

SELECT COUNT(*)
FROM dbo.scores
WHERE (testKey LIKE 'EN_PS1%')

 

[nigelrivett]

SELECT parentkey, count(*)
FROM dbo.scores
WHERE testKey LIKE 'EN_PS1%'
group by parentkey

Do you mean the _ to be a wildcard i.e. any character?
if not

SELECT parentkey, count(*)
FROM dbo.scores
WHERE testKey LIKE 'EN|_PS1%' escape '|'
group by parentkey

or

SELECT parentkey, count(*)
FROM dbo.scores
WHERE left(testKey,6) = 'EN_PS1'
group by parentkey


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[bostonkiwi]

Hi,

Thanks for the quick reply....this just returns the total number of entries that match EN_PS1%

I need the number of the number of entries that match EN_PS1% for each unique entry in 'parentkey'

i.e. there might be 5 different entries in 'parentkey' used many times.

parentkey1 = 100
parentkey2 = 76 etc