sql using Aggregates using a combination of min(), count(), having clause
-1
Trying to find code that will allow me to count first names by group, and then return the name with the lowest count.
Sample data:-
PersonGroup FirstName
------------------------
A Bob
A Mary
A Bob
A Bob
B Michelle
B Michelle
B Greg
B Greg
B Michelle
C Cindy
C Michelle
C Michelle
D Rod
D Rod
D Rod
D Rod
D Rod
D Mary
D Mary
D Mary
D Mary
D Mary
D Mary
Output required :
PersonGroup FirstName Count
--------------------------------
A Mary 1
B Greg 2
C Cindy 1
D Rod 5
First Name columns has the name that occurs the least within the group
Count column has the count of Name that occurs the least amount of times per group
this is my code so far, but every name is being returned,
select
PersonType,
FirstName,
count (firstName) as mycount
from
[Person].[Person]
group by
FirstName,
[PersonType]
having
count(firstName) = (select min(a.cnt)
from
(select count(firstname) as cnt
from [Person].[Person]
group by [FirstName]) as a)
order by
PersonType desc
sql count min having
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
add a comment |
-1
Trying to find code that will allow me to count first names by group, and then return the name with the lowest count.
Sample data:-
PersonGroup FirstName
------------------------
A Bob
A Mary
A Bob
A Bob
B Michelle
B Michelle
B Greg
B Greg
B Michelle
C Cindy
C Michelle
C Michelle
D Rod
D Rod
D Rod
D Rod
D Rod
D Mary
D Mary
D Mary
D Mary
D Mary
D Mary
Output required :
PersonGroup FirstName Count
--------------------------------
A Mary 1
B Greg 2
C Cindy 1
D Rod 5
First Name columns has the name that occurs the least within the group
Count column has the count of Name that occurs the least amount of times per group
this is my code so far, but every name is being returned,
select
PersonType,
FirstName,
count (firstName) as mycount
from
[Person].[Person]
group by
FirstName,
[PersonType]
having
count(firstName) = (select min(a.cnt)
from
(select count(firstname) as cnt
from [Person].[Person]
group by [FirstName]) as a)
order by
PersonType desc
sql count min having
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
1
What about ties? What do you want to return if two (or more) names only occur once for a group for instance?
– Thorsten Kettner
Nov 14 '18 at 6:52
1
With SQL questions you should always tag your DBMS. The non-standard brackets look like SQL Server. Is this the DBMS you are using?
– Thorsten Kettner
Nov 14 '18 at 6:54
Poor sample data. Remove on of the (B, Michelle) rows to make it a bit trickier! (And also adjust the expected result accordingly.)
– jarlh
Nov 14 '18 at 7:18
add a comment |
-1
-1
-1
Trying to find code that will allow me to count first names by group, and then return the name with the lowest count.
Sample data:-
PersonGroup FirstName
------------------------
A Bob
A Mary
A Bob
A Bob
B Michelle
B Michelle
B Greg
B Greg
B Michelle
C Cindy
C Michelle
C Michelle
D Rod
D Rod
D Rod
D Rod
D Rod
D Mary
D Mary
D Mary
D Mary
D Mary
D Mary
Output required :
PersonGroup FirstName Count
--------------------------------
A Mary 1
B Greg 2
C Cindy 1
D Rod 5
First Name columns has the name that occurs the least within the group
Count column has the count of Name that occurs the least amount of times per group
this is my code so far, but every name is being returned,
select
PersonType,
FirstName,
count (firstName) as mycount
from
[Person].[Person]
group by
FirstName,
[PersonType]
having
count(firstName) = (select min(a.cnt)
from
(select count(firstname) as cnt
from [Person].[Person]
group by [FirstName]) as a)
order by
PersonType desc
sql count min having
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
Trying to find code that will allow me to count first names by group, and then return the name with the lowest count.
Sample data:-
PersonGroup FirstName
------------------------
A Bob
A Mary
A Bob
A Bob
B Michelle
B Michelle
B Greg
B Greg
B Michelle
C Cindy
C Michelle
C Michelle
D Rod
D Rod
D Rod
D Rod
D Rod
D Mary
D Mary
D Mary
D Mary
D Mary
D Mary
Output required :
PersonGroup FirstName Count
--------------------------------
A Mary 1
B Greg 2
C Cindy 1
D Rod 5
First Name columns has the name that occurs the least within the group
Count column has the count of Name that occurs the least amount of times per group
this is my code so far, but every name is being returned,
select
PersonType,
FirstName,
count (firstName) as mycount
from
[Person].[Person]
group by
FirstName,
[PersonType]
having
count(firstName) = (select min(a.cnt)
from
(select count(firstname) as cnt
from [Person].[Person]
group by [FirstName]) as a)
order by
PersonType desc
sql count min having
sql count min having
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
edited Nov 14 '18 at 5:59
marc_s
575k12811091256
575k12811091256
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
asked Nov 14 '18 at 4:52
AmaranthAmaranth
11
11
1
What about ties? What do you want to return if two (or more) names only occur once for a group for instance?
– Thorsten Kettner
Nov 14 '18 at 6:52
1
With SQL questions you should always tag your DBMS. The non-standard brackets look like SQL Server. Is this the DBMS you are using?
– Thorsten Kettner
Nov 14 '18 at 6:54
Poor sample data. Remove on of the (B, Michelle) rows to make it a bit trickier! (And also adjust the expected result accordingly.)
– jarlh
Nov 14 '18 at 7:18
add a comment |
1
What about ties? What do you want to return if two (or more) names only occur once for a group for instance?
– Thorsten Kettner
Nov 14 '18 at 6:52
1
With SQL questions you should always tag your DBMS. The non-standard brackets look like SQL Server. Is this the DBMS you are using?
– Thorsten Kettner
Nov 14 '18 at 6:54
Poor sample data. Remove on of the (B, Michelle) rows to make it a bit trickier! (And also adjust the expected result accordingly.)
– jarlh
Nov 14 '18 at 7:18
1
1
What about ties? What do you want to return if two (or more) names only occur once for a group for instance?
– Thorsten Kettner
Nov 14 '18 at 6:52
What about ties? What do you want to return if two (or more) names only occur once for a group for instance?
– Thorsten Kettner
Nov 14 '18 at 6:52
1
1
With SQL questions you should always tag your DBMS. The non-standard brackets look like SQL Server. Is this the DBMS you are using?
– Thorsten Kettner
Nov 14 '18 at 6:54
With SQL questions you should always tag your DBMS. The non-standard brackets look like SQL Server. Is this the DBMS you are using?
– Thorsten Kettner
Nov 14 '18 at 6:54
Poor sample data. Remove on of the (B, Michelle) rows to make it a bit trickier! (And also adjust the expected result accordingly.)
– jarlh
Nov 14 '18 at 7:18
Poor sample data. Remove on of the (B, Michelle) rows to make it a bit trickier! (And also adjust the expected result accordingly.)
– jarlh
Nov 14 '18 at 7:18
add a comment |
2 Answers
2
active
oldest
votes
1
you can use row_number()
select a.*
from (select PersonType,FirstName ,count (firstName) as mycount,
row_number() over (partition by PersonType order by count(*)) as rn
from [Person].[Person]
group by FirstName,[PersonType]
) a
where rn= 1;
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
1
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
add a comment |
1
use window function row_number()
with cte as
(
select 'A' as PersonGroup, 'Bob' as name
union all
select 'A', 'Mary'
union all
select 'A', 'Mary'
union all
select 'B', 'Michelle'
union all
select 'B', 'Greg'
union all
select 'B', 'Greg'
union all
select 'B', 'Michelle'
union all
select 'B', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Cindy'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
)
, cte3 as (
select personGroup, name, COUNT(*) as cnt, row_number() over(partition by PersonGroup order by COUNT(*) ) rn from cte GROUP BY personGroup, name
) select PersonGroup,name,cnt from cte3 where rn=1
demo link
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
1
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53293369%2fsql-using-aggregates-using-a-combination-of-min-count-having-clause%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
you can use row_number()
select a.*
from (select PersonType,FirstName ,count (firstName) as mycount,
row_number() over (partition by PersonType order by count(*)) as rn
from [Person].[Person]
group by FirstName,[PersonType]
) a
where rn= 1;
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
1
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
add a comment |
1
you can use row_number()
select a.*
from (select PersonType,FirstName ,count (firstName) as mycount,
row_number() over (partition by PersonType order by count(*)) as rn
from [Person].[Person]
group by FirstName,[PersonType]
) a
where rn= 1;
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
1
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
add a comment |
1
1
1
you can use row_number()
select a.*
from (select PersonType,FirstName ,count (firstName) as mycount,
row_number() over (partition by PersonType order by count(*)) as rn
from [Person].[Person]
group by FirstName,[PersonType]
) a
where rn= 1;
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
you can use row_number()
select a.*
from (select PersonType,FirstName ,count (firstName) as mycount,
row_number() over (partition by PersonType order by count(*)) as rn
from [Person].[Person]
group by FirstName,[PersonType]
) a
where rn= 1;
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
edited Nov 14 '18 at 5:50
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
answered Nov 14 '18 at 5:19
fa06fa06
12.3k2917
12.3k2917
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
1
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
add a comment |
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
1
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
In the second method using row_number for all rows row number will be 1 . You need to remove the firstname partition from the over clause then it will work fine
– Sanal Sunny
Nov 14 '18 at 5:40
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
@SanalSunny, thank u - edited
– fa06
Nov 14 '18 at 5:44
1
1
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
Don't use the first one, it parses the whole table multiple times. There no need. That first answer is a bad answer. The second answer, however, is perfect :)
– MatBailie
Nov 14 '18 at 5:47
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
@MatBailie, thank u
– fa06
Nov 14 '18 at 5:50
add a comment |
1
use window function row_number()
with cte as
(
select 'A' as PersonGroup, 'Bob' as name
union all
select 'A', 'Mary'
union all
select 'A', 'Mary'
union all
select 'B', 'Michelle'
union all
select 'B', 'Greg'
union all
select 'B', 'Greg'
union all
select 'B', 'Michelle'
union all
select 'B', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Cindy'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
)
, cte3 as (
select personGroup, name, COUNT(*) as cnt, row_number() over(partition by PersonGroup order by COUNT(*) ) rn from cte GROUP BY personGroup, name
) select PersonGroup,name,cnt from cte3 where rn=1
demo link
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
1
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
add a comment |
1
use window function row_number()
with cte as
(
select 'A' as PersonGroup, 'Bob' as name
union all
select 'A', 'Mary'
union all
select 'A', 'Mary'
union all
select 'B', 'Michelle'
union all
select 'B', 'Greg'
union all
select 'B', 'Greg'
union all
select 'B', 'Michelle'
union all
select 'B', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Cindy'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
)
, cte3 as (
select personGroup, name, COUNT(*) as cnt, row_number() over(partition by PersonGroup order by COUNT(*) ) rn from cte GROUP BY personGroup, name
) select PersonGroup,name,cnt from cte3 where rn=1
demo link
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
1
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
add a comment |
1
1
1
use window function row_number()
with cte as
(
select 'A' as PersonGroup, 'Bob' as name
union all
select 'A', 'Mary'
union all
select 'A', 'Mary'
union all
select 'B', 'Michelle'
union all
select 'B', 'Greg'
union all
select 'B', 'Greg'
union all
select 'B', 'Michelle'
union all
select 'B', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Cindy'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
)
, cte3 as (
select personGroup, name, COUNT(*) as cnt, row_number() over(partition by PersonGroup order by COUNT(*) ) rn from cte GROUP BY personGroup, name
) select PersonGroup,name,cnt from cte3 where rn=1
demo link
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
use window function row_number()
with cte as
(
select 'A' as PersonGroup, 'Bob' as name
union all
select 'A', 'Mary'
union all
select 'A', 'Mary'
union all
select 'B', 'Michelle'
union all
select 'B', 'Greg'
union all
select 'B', 'Greg'
union all
select 'B', 'Michelle'
union all
select 'B', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Michelle'
union all
select 'C', 'Cindy'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Rod'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
union all
select 'D', 'Mary'
)
, cte3 as (
select personGroup, name, COUNT(*) as cnt, row_number() over(partition by PersonGroup order by COUNT(*) ) rn from cte GROUP BY personGroup, name
) select PersonGroup,name,cnt from cte3 where rn=1
demo link
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
edited Nov 14 '18 at 5:53
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
answered Nov 14 '18 at 5:23
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
12.5k2932
1
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
add a comment |
1
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
1
1
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
rextester.com/JFJ26973
– MatBailie
Nov 14 '18 at 5:45
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53293369%2fsql-using-aggregates-using-a-combination-of-min-count-having-clause%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What about ties? What do you want to return if two (or more) names only occur once for a group for instance?
– Thorsten Kettner
Nov 14 '18 at 6:52
1
With SQL questions you should always tag your DBMS. The non-standard brackets look like SQL Server. Is this the DBMS you are using?
– Thorsten Kettner
Nov 14 '18 at 6:54
Poor sample data. Remove on of the (B, Michelle) rows to make it a bit trickier! (And also adjust the expected result accordingly.)
– jarlh
Nov 14 '18 at 7:18