Home > Net >  Use top summed values from one column as a filter to group by another column in PostgreSQL
Use top summed values from one column as a filter to group by another column in PostgreSQL

Time:12-31

enter image description here

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
  • Related