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