SQL Get row that have maximum values over two columns

user_2740928 0
Viewed 83

I have a table like this in Snowflake. It supports ANSI SQL, so don't worry if this DB isn't familiar to you.

Salesman Customer Country
Brown Super Company UK
Brown Another customer UK
Smith Contoso US
Brown Test company US

I'd need to find where each salesman have most of customers. So desired response for the query would be like this.

Salesman Country cnt(country)
Brown UK 2
Smith US 1

I've come up with this

SELECT
   salesman,
   country,
   max(count(country))
FROM
   customertable
GROUP BY
   salesman, country

But nested aggeregation functions aren't supported. And I've already read quite good reasons for that. But I just cannot find a way to do that in any other way.

snowflake-cloud-data-platform
2 Answers

QUALIFY could be used to filter the highest value per salesman:

SELECT salesman,
       country,
       count(country) AS cnt
FROM customertable
GROUP BY salesman, country
QUALIFY RANK() OVER(PARTITION BY salesman ORDER BY cnt DESC) = 1
user_50708790

Regarding your questions i guess you would want to count customer by country instead of country. This should do the job with the use of WINDOW FUNCTIONS AND QUALIFY Window Functions documentation

CREATE OR REPLACE TABLE customers (salesman STRING, customer STRING, country STRING);

INSERT INTO customers
VALUES
('Brown', 'Super Company', 'UK'),
('Brown', ' Another customer', 'UK'),
('Smith', 'Contoso', 'US'),
('Brown', 'Test company', 'US')
;

SELECT
    salesman,
    country,
    COUNT(customer) AS nb_customer
FROM customers
GROUP BY
    salesman,
    country
QUALIFY RANK() OVER (PARTITION BY salesman ORDER BY nb_customer DESC) = 1
;
user_165267480
Related