dianna Felton
asked on
MS Access 2007 Count Unique Values in a Group
How would I count each unique value when the values in a group change. I would need to do a unique count based on the name / qty combination below.
Name Qty Example Count
Louis 1 1
Louis 12 2
Louis 18 3
Brianna 1 1
Cynde 1 1
Cynde 6 2
Name Qty Example Count
Louis 1 1
Louis 12 2
Louis 18 3
Brianna 1 1
Cynde 1 1
Cynde 6 2
Or perhaps:
SELECT Name, Qty, Count(*) AS ItemCount FROM YourTable GROUP BY Name, Qty
SELECT Name, Qty, Count(*) AS ItemCount FROM YourTable GROUP BY Name, Qty
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Select [name], Qty, (select count(*) From TableX as T where TableX.[Name]=T.[Name] and TableX.Qty >= T.Qty) as ItemCount
from TableX
order by [name]