asked on

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

Lee Wadwell

Hi twands,

try

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

lwadwell

Aneesh

select Person, sum(units) as totl
from urTable
group by Person

twands

ASKER

lwadwell

I have tried your solution, but to my knowledge "count(distinct ID) as idcnt" is not supported in access. It returns an error.

twands

ASKER

aneeshattingal:
Your solution gives me the person and number of units but not the count of unique record id's.

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

twands

ASKER

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.