Query of distinct values

I have a table that has the following values:
ID     Person      Units
128   Greg        8
129   Greg        10
130   Greg         5
131   Greg         6
131   Greg         4
132   Sally         10
133   Sally         5
133   Sally         8
133   Sally         15

I need to write a query that will sum up by person the number of unique Id's and the total number of units.
In this case, it should show:
     Greg     4 (id's)   33 (units)
     Sally     2 (id's)   38 (units)

The kicker is that the id's are not unique, so I need a count of the unique records for each person , but the sum of the units must contain all of the units.

Thanks




twandsAsked:
Who is Participating?
 
lwadwellConnect With a Mentor Commented:
twands,

OK ... that is a shame ... try this (not very nice I must admit)

SELECT t1.Person, t2.idcnt, SUM(t1.Units)
FROM yourtable t1
INNER JOIN (SELECT v1.Person, COUNT(*) as idcnt FROM (SELECT DISTINCT Person, ID FROM yourtable) v1 GROUP BY v1.Person) t2
ON t1.Person = t2.Person
GROUP BY t1.Person, t2.idcnt

lwadwell
0
 
lwadwellCommented:
Hi twands,

try

SELECT Person, count(distinct ID) as idcnt, SUM(Units)
FROM yourtable
GROUP BY Person

lwadwell
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select Person, sum(units) as totl
from urTable
group by Person
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
twandsAuthor Commented:
lwadwell

I have tried your solution, but to my knowledge "count(distinct ID) as idcnt" is not supported in access.  It returns an error.
0
 
twandsAuthor Commented:
aneeshattingal:
Your solution gives me the person and number of units but not the count of unique record id's.
0
 
twandsAuthor Commented:
Thanks much!  With a few minor tweaks, I was able to incorpoarte your solution into my query and it gives me what I was looking for.
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.