Selecting maximum value for a column with contraint

Question

Table is as follows

Company, Vertical, Counts

For each company I want to get the SUM of counts based on a specific Vertical having the highest count

Company       Vertical             Counts 
IBM           Finance              10
IBM           R&D                   5
IBM           PR                    2

I would like to get the following output

IBM       Finance   17

Your question is inconsistent. "I want to get the SUM of counts based on a specific Vertical having the highest count" seems you want finance = 10, but you then say you want finance = 17. Do you want the vertical or the company? — Denis de Bernardy, Jun 09 '11 at 22:27

score 1 · Answer 1 · answered Jun 09 '11 at 22:15

1

A self-join should do it.

select company, vertical, total_count
from(
    select sum(counts) as total_count
    from table
    )a
cross join table
where counts=(select max(counts) from table);

Depending on your RDBMS, you can also use a window function (eg sum(count) over () as total_count) and not have to worry about the cross join.

answered Jun 09 '11 at 22:15

Won't give correct output: how does this extract the finance row? – gbn Jun 11 '11 at 07:49
Well, counts=(select max(counts) from table) precisely when vertical='Finance', therefore the row produced by my query above will be 'IBM','Finance',17. – Jun 13 '11 at 14:39
ah, I thought I'd mentioned "if there was more than one company" – gbn Jun 13 '11 at 14:50

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

It's a twist on the problem of "How to get the MAX row" (DBA.SE link)

get total and highest vertical per Company in a simple aggregate
use these to identify the row in the source table

Something like this, untested

SELECT
    t.Company, t.Vertical, m.CompanyCount
FROM
    ( --get total and highest vertical  per Company
    SELECT
        COUNT(*) AS CompanyCount,
        MAX(Vertical) AS CompanyMaxVertical,
        Company
    FROM MyTable
    GROUP BY Company
    ) m
    JOIN --back to get the row for that company with  highest vertical
    MyTable t ON m.Company = t.Company AND m.CompanyMaxVertical = t.Vertical

Edit: this is closer to standard SQL than a ROW_NUMBER because we don't know the platform

score 0 · Answer 3 · answered Jun 11 '11 at 09:38

select Company,
       Vertical,
       SumCounts
from (       
        select Company,
               Vertical,
               row_number() over(partition by Company order by Counts desc) as rn,
               sum(Counts) over(partition by Company) as SumCounts
        from YourTable
     ) as T
where rn = 1

score 0 · Answer 4 · answered Jun 11 '11 at 10:31

SELECT company,
       vertical,
       total_sum
FROM (
    SELECT Company, 
           Vertical, 
           sum(counts) over (partition by null) as total_sum,
           rank() over (order by counts desc) as count_rank
    FROM the_table
) t
WHERE count_rank = 1

Selecting maximum value for a column with contraint

4 Answers4