?
Solved

Query of distinct values

Posted on 2009-05-07
6
Medium Priority
?
252 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
Comment
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

by:lwadwell
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

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

Author Comment

by:twands
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:twands
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

by:
lwadwell earned 1000 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

by:twands
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

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

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

Join & Ask a Question