count and group with a distinct field

[wengang]

Hi all.
I've been trying to do a simple (should be) query to count grouped records:

Select field1, count(field 1) as total from Table1 where (conditions) group by Field 1.

The problem is I have field2 that has to be distinct. In other words if there is already a given field2 value in any group, no other records with that field 2 value should be in the group.

I've tried to use a subquery, but I keep getting confused. I think there should be a sub query of distinct field2 values that meet conditions, then an outer query that does the grouping. But my results either come up with errors or include records that don't meet conditions. Any thoughts?
Thanks
Wengang

[Zvoni]

Uhh,... haven't understood a word of it.

Any sample data?
That's what i have, and that's what it should look like....

[wengang]

It's work stuff, so this is a made up example:

Suppose there are 3 fields
Name. Team. PhD
John. 1. Yes
Joe. 2. No
Jack. 3. Yes
Jim. 4. No
John 3. Yes
Jay. 4. No
John. 1. Yes

Ok. So there are four teams. Some members have PhDs and some don't.
Some people like John are in the list more than once and deduplicating the data table is not an option. There are other fields besides the ones listed and they may not be identical values. So long story short, the data appears as above and can't be modified.

Now the query. How many people on each team have PhDs?
If there were no duplicates, i could just group by team and count names where PhD =true.
But i can't have John counting multiple times. So each individual should only be counted in his team once. Of course if John is also on team 3 sometimes, then he can be counted once for each team he is on. He just can't count on the same team twice. Assume for this made up example that there will never be two people named John. It's always the same guy.

I just keep confusing myself when I try to write the query.

[techgnome]

So simplify it:
get the distinct data first:

Select distinct Name, Team, PhD from YourTable

That gives you this, right?
John. 1. Yes
Joe. 2. No
Jack. 3. Yes
Jim. 4. No
John 3. Yes
Jay. 4. No


So then group by team, and count the PhDs... this will replace the PhDs="yes" with a 1 and sum them up, essentially counting them.

Select team, sum(case when PhD = 'Yes' then 1 else 0 end) PhDCount
from (select distinct name, team, phd from yourtable) dta
group by team

badda boom, badda bing.

-tg

[wengang]

I started several times with select distinct in a sub query but couldn't bring it home. Thanks. Is dta part of your query?

[wengang]

I'm also wondering if writing select distinct name will cause John's name to only be counted once. I do want it counted only once for each team he is on, but not once overall (unless he is only on one team).

[techgnome]

I should only count distinct ROWS ...but if you're paranoid about it...
try this:

Select team, sum(case when PhD = 'Yes' then 1 else 0 end) PhDCount
from (select name, team, phd from yourtable group by name, team, phd) dta
group by team

That will definitely flatten the data and drop the duplicate rows.

And yes, dta is part of the query... it's an alias for the subquery.

-tg

[wengang]

I had another thought about this after your first reply. I just did a select distinct [name] & [team].
That way the same guy could be counted more than once as long as it was for another team. Your solution got me back on the right track, though. Thanks!

[FunkyDexter]

I think you're over complicating this but it does depend on what database you're using. SQL Server (and most of the big players) support a count distinct:-

Select Team, count(distinct Name) as count
From yourtable
Where Phd = 'Yes'
Group By Team