Use top summed values from one column as a filter to group by another column in PostgreSQL-CodePudding

I want to find the top 5 products by total sales and then look at sales by region of only those 5 products.

I can do it in two steps by finding top sales by product:

-- # step 1: Identify top 5 products by sales
SELECT 
    Product,
    sum(sales) totsales
FROM 
    drug_data.sales_data
GROUP BY 
    Product
ORDER By
    totsales DESC
LIMIT
    5;

Step 1 shows that the 5 top products are M, C, H, J, and G. Knowing this I can use WHERE product IN to get what I want:

-- # Step 2: manually type out the custom WHERE Product IN statement.
SELECT
    Product,
    Region,
    sum(sales) totSalesByRegion
FROM 
    drug_data.sales_data
WHERE 
    Product IN ('M', 'C', 'H', 'J', 'G')
Group BY
    Product,
    Region
ORDER BY
    totSalesByRegion DESC

Is there a way to run this in one step, using the totsales column from the first step as the list in the IN filter of the second step?

I want to automate this so that I can easily change the number of top products to include.

CodePudding user response：

The following is untested (your sample data is not consumable because it's an image) however the following should work - define your required list to filter on in a CTE and then join with this in your main query:

with t as (
    select Product
    from drug_data.sales_data
    group by Product
    order by sum(sales) desc
    limit 5
)
select
    Product,
    Region,
    Sum(sales) totSalesByRegion
from drug_data.sales_data d
where exists (
    select * from t
    where t.Product = d.product
)
group by Product, Region
order by totSalesByRegion desc