CODEWITHSUNDEEP
mysql
Osca
Osca

Reputation: 1694

Apply group by on result of window function

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

Upvotes: 1

Views: 30

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

Your way of calculating percent is bit weird. It seems that you are identifying contribution of particular transaction in overall profit.

Anyways, you can simply use your existing query's result-set as a Derived Table, and do a Group By using Year() function, to calculate the Avg():

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

Upvotes: 2

Related Questions