>A select like this will return all the uniques values of NoMember:
>
>
>SELECT Client.NoMember
> FROM Client
> WHERE Client.Active=1
> GROUP BY Client.NoMember
>
>
>Now, let's assume the goal is to obtain the count.
>
>The following would not work:
>
>
>SELECT COUNT(*) AS RecordCount
> FROM Client
> WHERE Client.Active=1
> GROUP BY Client.NoMember
>
>
>...as it will return all records related to the group and that the GROUP BY itself.
>
>In my example, I have 19 records for the same member. So, the first select returns one record saying that Client.NoMember found is 1. The second select returns a count of 19.
>
>What would be the best approach to count over the group by? Basically, I would like to obtain 1 as all those 19 records are from the same member.
You're looking at COUNT DISTINCT (*)
If it's not broken, fix it until it is.
My Blog