I have a problem where we have certain stores in different locations, and I want to output the store for each location that has the max sales.

For example:

Here I can use: select Location, Store, sum(price) from table1

To get

But what I want, is the output to only return the store in each location that has the highest sales:

How can I get to this?

CodePudding user response：

You can use a query like this

SELECT 
    location,
    store,
    sum_price
FROM (    
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY location ORDER BY sum_price DESC) AS rn
    FROM (
        SELECT
            location,
            store,
            SUM(price) AS sum_price
        FROM data
        GROUP BY location, store
    ) loc_store_sum_price
) loc_store_sum_price_rn
WHERE rn = 1

You can check a working demo here

CodePudding user response：

I'm using this data for my example (tested on postgresql):

INSERT INTO sales(store_location, store_name, price)
VALUES
('A', 'x', 1.99),
('A', 'x', 3.99),
('A', 'y', 2.99),
('B', 'd', 3.99),
('B', 'e', 5.99),
('B', 'e', 1.99),
('C', 'f', 2.99),
('C', 'g', 3.05);

This is how you sum all the prices by store:

SELECT store_location, store_name, sum(price) FROM sales GROUP BY store_location, store_name

A   x   5,98
C   g   3,05
C   f   2,99
A   y   2,99
B   d   3,99
B   e   7,98

Therefore, the max price sum for location can be found like that:

SELECT store_location AS grouped_loc, max(sum) as grouped_price_sum FROM (
    SELECT store_location, store_name, sum(price) FROM sales GROUP BY store_location, store_name
) max_by_store GROUP BY store_location

B   7,98
C   3,05
A   5,98

Now, you just have to attach the store name to it:

SELECT grouped_loc, store_name, grouped_price_sum FROM (
    SELECT store_location AS grouped_loc, max(sum) as grouped_price_sum FROM (
        SELECT store_location, store_name, sum(price) FROM sales GROUP BY store_location, store_name
    ) max_by_store GROUP BY store_location 
) max_price_by_location 
INNER JOIN (
    SELECT store_location as loc, store_name, sum(price) as price_sum FROM sales GROUP BY store_location, store_name
) max_by_store_second 
ON max_price_by_location.grouped_loc=max_by_store_second.loc AND max_price_by_location.grouped_price_sum=max_by_store_second.price_sum

A   x   5,98
C   g   3,05
B   e   7,98