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

ppostonPresident/OwnerAsked:
Who is Participating?
 
ppostonPresident/OwnerAuthor Commented:
@coc varchar(15),
@dStart datetime = 'mm/dd/yyyy',
@dEnd datetime ='mm/dd/yyyy',
@churchList varchar (1000)

AS

SELECT count(DISTINCT (c.CharityCkID))
&#9; , z.City
&#9; , z.State
&#9; , z.ZipCode
&#9; , z.County

FROM
&#9;client c
&#9;LEFT JOIN ZipCodeDatabase_STANDARD z
&#9;&#9;ON c.zip = z.ZipCode
&#9;JOIN activity a
&#9;&#9;ON c.CharityCkID = a.CharityCkID

WHERE
&#9;a.charityckid IN (SELECT DISTINCT (a.CharityCkID)
&#9;&#9;&#9;&#9;&#9;  FROM
&#9;&#9;&#9;&#9;&#9;&#9;  activity a INNER JOIN users u ON u.username=a.username 
&#9;&#9;&#9;&#9;&#9;&#9;  JOIN UserCOC on UserId=u.id JOIN

&#9;&#9;&#9;&#9;&#9;&#9;  dbo.fn_SplitString(@churchList, ',') list
&#9;&#9;&#9;&#9;&#9;&#9;&#9;  ON list.StringValue = church
&#9;&#9;&#9;&#9;&#9;  WHERE
&#9;&#9;&#9;&#9;&#9;&#9;  (COC=@coc) AND
&#9;&#9;&#9;&#9;&#9;&#9;  (a.date >= @dStart)
&#9;&#9;&#9;&#9;&#9;&#9;  AND (a.date <= @dEnd)
&#9;&#9;&#9;&#9;&#9;&#9;  AND (c.zip = z.ZipCode))

GROUP BY
&#9;z.ZipCode
  , z.City
  , z.State
  , z.County

Open in new window

0
 
lwadwellCommented:
try
   COUNT(DISTINCT c.zip)
0
 
ppostonPresident/OwnerAuthor Commented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SharathData EngineerCommented:
Can you provide some sample data from three tables and the expected result?
0
 
lwadwellCommented:
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?
0
 
ppostonPresident/OwnerAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.