-
Apr 8th, 2019, 05:45 AM
#1
Thread Starter
Frenzied Member
count and group with a distinct field
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
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Apr 8th, 2019, 07:08 AM
#2
Re: count and group with a distinct field
Uhh,... haven't understood a word of it.
Any sample data?
That's what i have, and that's what it should look like....
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 8th, 2019, 11:05 AM
#3
Thread Starter
Frenzied Member
Re: count and group with a distinct field
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.
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Apr 8th, 2019, 11:25 AM
#4
Re: count and group with a distinct field
So simplify it:
get the distinct data first:
Code:
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.
Code:
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
-
Apr 8th, 2019, 12:14 PM
#5
Thread Starter
Frenzied Member
Re: count and group with a distinct field
I started several times with select distinct in a sub query but couldn't bring it home. Thanks. Is dta part of your query?
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Apr 8th, 2019, 12:17 PM
#6
Thread Starter
Frenzied Member
Re: count and group with a distinct field
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).
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Apr 8th, 2019, 02:19 PM
#7
Re: count and group with a distinct field
I should only count distinct ROWS ...but if you're paranoid about it...
try this:
Code:
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
-
Apr 9th, 2019, 08:58 AM
#8
Thread Starter
Frenzied Member
Re: count and group with a distinct field
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!
Wen Gang, Programmer
VB6, QB, HTML, ASP, VBScript, Visual C++, Java
-
Apr 10th, 2019, 08:47 AM
#9
Re: count and group with a distinct field
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:-
Code:
Select Team, count(distinct Name) as count
From yourtable
Where Phd = 'Yes'
Group By Team
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|