Solved

# Query of distinct values

Posted on 2009-05-07
251 Views
Last Modified: 2013-11-27
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

0
Question by:twands
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 3
• 2
6 Comments

LVL 25

Expert Comment

ID: 24332753
Hi twands,

try

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

lwadwell
0

LVL 75

Expert Comment

ID: 24332758
select Person, sum(units) as totl
from urTable
group by Person
0

Author Comment

ID: 24332813
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

Author Comment

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

LVL 25

Accepted Solution

lwadwell earned 250 total points
ID: 24332838
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

Author Closing Comment

ID: 31579298
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month2 days, 18 hours left to enroll

#### 695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.