Link to home
Start Free TrialLog in
Avatar of pposton
ppostonFlag for United States of America

asked on

Counting Distinct Values

I have basically 3 tables, #1 holds the client information, #2 holds records concerning the client and #3 a list of zipcode information.

I am trying to build a query which considers the client only once regardless of how many records they have in the "activity" table.  They need at least one record in the activity table however, to be counted.  Then I pull information from the "zipcode" table on the county, city, etc.  Basically when it's done I return a result like:
37372 = 10
73658 = 5
65891 = 12

The query I've built seems to be pulling too many numbers.  Comparing against a small data set I am pulling something like:
37372=4
64891=1

But when I pull the data through a simpler query I can see that 1 client had no zip (so they shouldn't be considered) 1 client had 2 entries (so they should be counted once) and another client with has 1 activity record (so they should be counted once).  It should be showing:
37372=1
64891=1

I hope this is not too jumbled of an explanation but I'm trying to give as much background information as I can.  The current query is posted below.

Thanks for any help.

@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',
@churchList varchar (1000)

AS

SELECT c.zip
	 , count(c.zip) AS zipCount
	 , z.City
	 , z.State
	 , z.County


FROM
	client c
	JOIN ZipCodeDatabase_STANDARD z
		ON zip = ZipCode
WHERE
	EXISTS (SELECT NULL
			FROM
				activity a
				JOIN users u
					ON u.username = a.username
				JOIN dbo.fn_SplitString(@churchList, ',') list
					ON list.StringValue = church



			WHERE
				(a.CharityCkID = c.CharityCkID)
				AND (c.zip = z.ZipCode)
				AND (a.date >= @dStart)
				AND (a.date <= @dEnd))

GROUP BY
	zip
  , z.City
  , z.State
  , z.County

Open in new window

Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

try
   COUNT(DISTINCT c.zip)
Avatar of pposton

ASKER

Using the "COUNT(DISTINCT c.zip) eliminates all except 1 response per zip code.  It needs to count a distinct activity record, then pull the client zip code data.  That way it counts everybodys zip, but just counts the individual once, not the zip code itself
Can you provide some sample data from three tables and the expected result?
What is it trying to do?  Is it count the number of clients within a particular zipcode with activity?
having "AND (c.zip = z.ZipCode)" in the exists sub-query confuses me.

I you remove the group by and just do a SELECT * ... are the right number of rows being returned in total with no duplicates?
ASKER CERTIFIED SOLUTION
Avatar of pposton
pposton
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pposton

ASKER

pposton requested that this question be closed as follows:

Accepted answer: 0 points for pposton's comment #a38379903

Since a comment selected as the solution has been deleted, our Moderators have been contacted to resolve the closure of this question.