Counting problem

Hi everyone,

another problem:

I'm trying to count the number of rows but it's not working. Here's my code:

SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
(
 SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
 FROM [Activites]
)

As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.

Any help?
AndrewHi,

i hope the following query will solve your issue.

select count(*) from activities group by personid

Regards
Ravi|||It's actually doing exactly what you are asking it to do: count the total number of rows in activities where personid exists inthe activities table.

What you REALLY want it to do is to count the DISTINCT people in that table.

Try:

SELECT 'TOTAL number of rows', count(DISTINCT PersonID)
FROM [Activities]

The GROUP BY solution works great, too...|||Thanks heaps for the feedback!!

Cheers
Andrew