Average of rows in SQL-CodePudding

here my table Table

I want to find the average of MTK IPA PJOK table 2

with this code

SELECT induk, nama, kelas,
       SUM(if (kdmapel=“111”,ratarata,0)) as MTK,
       SUM(if (kdmapel=“112”,ratarata,0)) as IPA,
       SUM(if (kdmapel=“115”,ratarata,0)) as PJOK,
       ((MTK IPA PJOK)/3) as NAR
from nilai
WHERE kelas=1 and semester=“ganjil”
group by induk

but it doesn't work, the error said

#1054 - Unknown column 'MTK' in 'field list'

CodePudding user response：

The MTK, IPA and PJOK are calculated from group functions.
So they can't be used directly like that.

One way is to wrap it all in a sub-query, then calculate NAR in the outer query.

However, their calculation is quite simple and similar.
So the calculation for NAR isn't much harder.

SELECT induk, nama, kelas,
       SUM(if(kdmapel='111',ratarata,0)) as MTK,
       SUM(if(kdmapel='112',ratarata,0)) as IPA,
       SUM(if(kdmapel='115',ratarata,0)) as PJOK,
       (SUM(if(kdmapel IN('111','112','115'),ratarata,0))/3) as NAR
FROM nilai
WHERE kelas=1 and semester='ganjil'
GROUP BY induk

CodePudding user response：

Looks like you are referencing the alias name and it doesn’t know what that is.

Either write it as a CTE and then add the alias columns together or first store the results in a temp table and then add them.

Something like this

;With myCte as
(

SELECT induk, 
       nama,
       kelas,
       SUM(if (kdmapel='111',ratarata,0)) as MTK, 
       SUM(if (kdmapel='112',ratarata,0)) as IPA, 
       SUM(if (kdmapel='115',ratarata,0)) as PJOK
      from nilai WHERE kelas=1 and semester='ganjil' 
      group by induk

)
Select *,
 ((MTK IPA PJOK)/3) as NAR
  From myCte