Solved

Query of distinct values

Posted on 2009-05-07
6
247 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 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

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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