pposton
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.
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
COUNT(DISTINCT c.zip)