• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

SQL Distinct Count

I have data that I need to do a distinct count and I don't seem to be getting the right counts.

Sample of data:

Name, Role
John, PM
John, BA
Joe, PM
Joe, PM
John, BA

The results that I expect are
PM - 2
BA - 1

But I am not getting that.  I am getting
PM - 3
BA - 2

I am working in Access...Any thoughts?
0
tobey1
Asked:
tobey1
1 Solution
 
LimbeckCommented:
hm thats because you group by the role; not by role/name

select role,[name],count(*) as cnt
from yourtable
group by role,[name]
0
 
tobey1Author Commented:
That Gives me the following:

role, name, cnt
BA, John, 2
PM, Joe, 2
PM, John, 1

The results needs to be
Role, Cnt
BA - 1
PM - 2
0
 
Jinesh KamdarCommented:
Whats the criteria to decide that the count for BA should be 1 ?
0
 
TextReportCommented:
I would say the SQL is returning the correct figures but you need to get the records distinct before counting them

select role,count(*) as cnt
from (SELECT Role, Name FROM TableName GROUP BY Role, Name)
group by role
0
 
tobey1Author Commented:
The criteria is, if a name holds the same role, then it is one, if a name holds 2 roles, then that is 1 for each role.  Same as if a there are 3 names for 1 role, then the role is 3.

Textreport Nailed It!

Thanks All.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now