Apply group by on result of window function

3218 views mysql
6

I used window function to calculate each product's profit percentage

SELECT 
    productCode, productProfit, paymentDate, productName,
    productProfit/sum(productProfit)  OVER (PARTITION BY productCode) AS percent
FROM
    profit;

The output enter image description here

The next step, I want to calculate AVG(percent). How can I it into the first statement?

The result will look like this enter image description here

answered question

What would be expected output ? Add it in formatted text.

1 Answer

4

You possibly want this:

SELECT 
  YEAR(dt.paymentDate) AS payment_date_year, 
  AVG(dt.percent) AS average_profit_percent 
FROM 
(
  SELECT 
    productCode, 
    productProfit, 
    paymentDate, 
    productName,
    productProfit/sum(productProfit)  OVER (PARTITION BY productCode) AS percent
  FROM
    profit
) AS dt
GROUP BY 
  payment_date_year

posted this

Have an answer?

JD

Please login first before posting an answer.