sql using Aggregates using a combination of min(), count(), having clause

Question

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

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 06:52
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 06: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 07:18

Fahmi · Answer 1 · 2018-11-14T05:50:35.447

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;

edited Nov 14 '18 at 05:50

answered Nov 14 '18 at 05:19

Fahmi

37,315
5
22
31

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 05:40
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 05:47

Zaynul Abadin Tuhin · Answer 2 · 2018-11-14T05:53:42.827

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

sql using Aggregates using a combination of min(), count(), having clause

2 Answers2