CODEWITHSUNDEEP
sqlaverage
Benne
Benne

Reputation: 47

How to calculate the average per year in a nested query?

I would like to calculate a year average, the situation is as follows. 

 Plant_ID | Year | Month  |  MR   | Range  
  CCAR    | 2009 | 1      | 0,706 | Null  
  CCAR    | 2009 | 2      | 0,626 | 0,08  
  CCAR    | 2009 | 3      | 0,637 | 0,11  
  CCAR    | 2009 | 4      | 0,737 | 0,1  
  CCAR    | 2009 | 5      | 0,552 | 0,19  
  CCAR    | 2009 | 6      | 0,418 | 0,137  
  CCAR    | 2009 | 7      | 0,503 | 0,085  
  CCAR    | 2009 | 8      | 0,645 | 0,058  
  CCAR    | 2009 | 9      | 0,743 | 0,098  
  CCAR    | 2009 | 10     | 0,556 | 0,187  
  CCAR    | 2009 | 11     | 0,298 | 0,258  
  CCAR    | 2009 | 12     | 0,339 | 0,041  
  CCAR    | 2010 | 1      | 0,381 | 0,042  
  Etc.

I would like to add two columns; 1(aMR) that calcutes the average MR (Maintenance Rate) per year, so one average for 2009, one for 2010, etc. and the second that calculates the average Range per year. My problem is I don't know where to do this in my query, because it got a little more complicated then I can handle.... I already made a statement to add a colum aMR, which is the average of MR (see query code below). But I can't get an averge per year (now it returns a total average of all MR values) Can anyone please help me with this?

WITH cte AS (
SELECT Plant_Id, 
       Jaar, 
       Maand, 
      (SUM(Compl) + 0.000) / SUM(Total) AS MR,
       ROW_NUMBER() OVER (ORDER BY Jaar DESC, 
       Maand DESC) AS RowNumber  
FROM (SELECT Plant_Id, 
             Jaar, 
             Late, 
             EarlyJobs, 
             OnTimeJobs, 
             Maand, SUM(EarlyJobs +OnTimeJobs) AS Compl, 
             SUM(EarlyJobs) + SUM(Late) + SUM(OnTimeJobs) AS Total  
      FROM MaintenanceRatebepaen AS MaintenanceRatebepaen_1  
      GROUP BY Plant_Id, Jaar, Maand, Late, OnTimeJobs, EarlyJobs) AS MaintenanceRatebepaen  
WHERE (Jaar >= 2009) AND (Jaar <= 2011) AND (Plant_Id = 'CCAR')  
GROUP BY Jaar, Plant_Id, Maand) 

SELECT d1.Plant_Id, 
       d1.Jaar, 
       d1.Maand, 
       d1.MR, 
       abs(d1.MR - d2.MR) AS [ChangeMRFromPreviousMonth], 
       aMR =(SELECT avg(cte.MR) FROM cte)  
FROM cte d1 
LEFT OUTER JOIN cte d2 ON d2.RowNumber = (d1.RowNumber + 1)  
ORDER BY d1.Rownumber DESC

So what I would like is to add two columns; one that calculates the average of MR (aMR) per year and second a column that calculates the average Range per year. Can anyone please help me with this? I'm pretty new to SQL and any help will be greatly appreciated!

Upvotes: 2

Views: 2725

Answers (3)

Gerardo Lima
Gerardo Lima

Reputation: 6703

if you can get to a table like myTable (Plant_ID, Year, Month, MR, Range), you've already done most of the job:

SELECT year, AVG(MR) AS "avg_mr", AVG(range) AS "avr_range"
FROM myTable
GROUP BY year;

If this data is result from a complex query just replace this hypothetical table name for the query:

SELECT year, AVG(MR) AS "avg_mr", AVG(range) AS "avr_range"
FROM (
    SELECT ... FROM ... -- complex query goes here
)
GROUP BY year;

Upvotes: 2

user359135
user359135

Reputation: 

SELECT 
    PLant_id
    ,year
    ,avg(mr) as avgMR
    ,avg(range) as avgRange
 FROM 
    table
 GROUP BY
  Plant_id,year

you could then join to this to calculate variance or similar

SELECT 
    t.*
    ,c.*
    ,t.mr - c.avgMR as MRvariance
    ,t.Range - c.avgRange as RangeVariance
 FROM
    table as t
        INNER JOIN (
            SELECT 
                PLant_id
                ,year
                ,avg(mr) as avgMR
                ,avg(range) as avgRange
            FROM 
                table
            GROUP BY
                Plant_id,year
      ) as c
          ON c.plant_id = t.plant_id
          AND c.year = t.year

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

Try replacing aMR =(SELECT avg(cte.MR) FROM cte) with

aMR = avg (d1.MR) over (partition by d1.Plant_Id, d1.Jaar, d1.Maand)

I'm unsure about d1.Maand. If it means month, remove it from partition by. As for Range (or Change?), you might also try:

aChange = avg (d1.MR - d2.MR) over (partition by d1.Plant_Id, d1.Jaar, d1.Maand)

Upvotes: 0

Related Questions