CODEWITHSUNDEEP
sqlsql-server
Be Kind To New Users
Be Kind To New Users

Reputation: 10063

Count of the max value in an aggregate select

I want to count the number of the number of the maximum value of the minimum values in a particular field.

This select is broken if I were to uncomment the lines:

select clientnumber
      ,count(distinct clientname) as NumberUnique
      ,max(clientname) as UniqueSample1
      -- ,sum(case clientname when min(clientname) then 1 else 0 end) as UniqueCount1
      ,min(clientname) as UniqueSample2
      -- ,sum(case clientname when min(clientname) then 1 else 0 end) as UniqueCount2
  FROM dbo.InvoicesSent dt
 group by clientnumber
 having count(distinct clientname) > 1

Background: We are reviewing records that have two different names on the invoices for the same clientnumber. Knowing which one of the two has the higher count is handy for the analysis.

Note: I know there are other solutions that can take into account more than 2 unique values but at this point, I want to solve this problem for its academic value. I also know I could solve this with a subselect but I am holding out for some graceful solution.

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you know that there are two names, then you could do this without a subselect:

select top (1) with ties clientnumber, 
       min(clientname) over (partition by clientnumber) as min_name,
       max(clientname) over (partition by clientnumber) as max_name,
       (case when clientname = min(clientname) over (partition by clientnumber)
             then count(*)
             else sum(count(*)) over (partition by clientnumber) - count(*)
        end) as min_count,
       (case when clientname = max(clientname) over (partition by clientnumber)
             then count(*)
             else sum(count(*)) over (partition by clientnumber) - count(*)
        end) as max_count
from InvoicesSent i
group by clientnumber, clientname
order by row_number() over (partition by clientnumber order by clientname) +
         (case when min(clientname) over (partition by clientnumber) = max(clientname) over (partition by clientnumber) then 1 else 0 end);

I am not arguing that this is elegant. Merely that it is possible for your conditions.

Here is a slightly simpler form:

select top (1) with ties clientnumber, 
       clientname as min_name,
       max(clientname) over (partition by clientnumber) as max_name,
       count(*) as min_count,
       sum(count(*)) over (partition by clientnumber) - count(*) max_count
from InvoicesSent i
group by clientnumber, clientname
order by row_number() over (partition by clientnumber order by clientname) +
         (case when count(*) over (partition by clientnumber) >= 2 then 0 else 1 end)

And a db<>fiddle.

Actually, this isn't so bad. It is aggregating by the name column and the client, putting all the data on one row using window functions and then selecting the one row. There is a little trick in the order by to only select clients with more than one name.

Upvotes: 2

George Joseph
George Joseph

Reputation: 5922

If you are making use of sql server that supports string_agg the following can be done

Here is a db fiddble link https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4184c994d37e0dc7178212cd657ba15f

create table invoices(id int identity, clientnumber int, clientname varchar(100));

insert into invoices(clientnumber,clientname)
select t.clientnumber,clientname
  from (values(100,'Adam')
             ,(100,'Adam')
             ,(100,'Abraham')
             ,(101,'Grace')
             ,(101,'Grace')
             ,(102,'Paul')
       )t(clientnumber,clientname);



select clientnumber
      ,string_agg(concat(clientname,'-',cnt),';') as clientname_and_cnt
 from(     
select distinct
       clientnumber
      ,clientname
      ,count(*) over(partition by clientnumber,clientname) as cnt
  from invoices
     )x
group by clientnumber     
order by 2 desc

Output
+--------------+--------------------+
| clientnumber | clientname_and_cnt |
+--------------+--------------------+
|          102 | Paul-1             |
|          101 | Grace-2            |
|          100 | Abraham-1;Adam-2   |
+--------------+--------------------+

And if you want to show up those which have two or more clientnames by clientnumber then Add the following in the having clause

having(charindex(';',string_agg(concat(clientname,'-',cnt),';')))<>0

If you would like ordering based on the number of invoices by the clientname then you may also do the following (within group clause)

select clientnumber
      ,string_agg(concat(clientname,'-',cnt),';') within group(order by cnt desc) as clientname_and_cnt
 from(     
select distinct
       clientnumber
      ,clientname
      ,count(*) over(partition by clientnumber,clientname) as cnt
  from invoices
     )x
group by clientnumber     
order by 2 desc

Upvotes: 0

Related Questions