mysql sql avg function does not return correct values?

Question

Now I want to produce a table that show final average of each student by formula:

Final_avg=0.15x(average of exam= test1,test2 and test3) 
         +0.25x(exam=midterm)+0.6x(exam=terminal);

I've tried this query but I'm getting incorrect values of fainal_avg

select e.sname AS sn 
     , AVG( CASE WHEN e.exam IN ('test1','test2','test4') THEN e.average END )  AS t_avg
     , e3.average as mid
     , e2.average as main
     , 0.15*e.average+0.25*e3.average+0.6*e2.average AS f_avg 
  from $form as e 
  JOIN $form as e2 
    ON e2.sname = e.sname AND e2.exam = 'terminal'
  JOIN $form as e3 
    ON e3.sname = e.sname AND e3.exam = 'midterm'
 group by e.sname 
 order by f_avg  desc

The final average that I get from the query is inncorect with one that I get by calculating using calculator

This is table for correct and incorrect final average

First read stackoverflow.com/help/how-to-ask and then edit your question. — jarlh
– jarlh, Commented May 22, 2017 at 9:29
And then see: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented May 22, 2017 at 9:38
Are you trying to do average of averages? Mathematically that doesn't make sense to me. It will never give you the right answer — India.Rocket
– India.Rocket, Commented May 22, 2017 at 9:43
You forgot to tell us what you are trying to calculate. Why do you consider some results wrong? Please show some rows with sample data (just for one sname, term and year) and show us the value you get and the one you want to get instead. — Thorsten Kettner
– Thorsten Kettner, Commented May 22, 2017 at 10:47

Thorsten Kettner · Accepted Answer · 2017-05-22 11:18:40Z

You are grouping by e.sname, but access e.average in your select clause. As there are several records per sname, it is left to chance which value the DBMS picks from them. You want an aggregation such as AVG(e.sname) instead, in your case: AVG(CASE WHEN e.exam IN ('test1','test2','test4') THEN e.average END).

This mix of self joins and conditional aggregation is kind of clumsy, however. I suggest either to aggregate your test records before joining or not joining at all instead.

Aggregate test records before joining

select
  sname, 0.15 * tst.average + 0.25 * mid.average + 0.6 * trm.average as result
from
(
  select sname, avg(average) as average
  from $form
  where exam in ('test1','test2','test4')
  group by sname
) tst
join $form trm on trm.sname = tst.sname and exam = 'terminal'
join $form mid on mid.sname = tst.sname and exam = 'midterm';

Mere aggregation, no join

select
  sname, 
  0.15 * avg(case when exam in ('test1','test2','test4') then average end) + 
  0.25 * avg(case when exam = 'midterm' then average end) + 
  0.6 * avg(case when exam = 'terminal' then average end) as result
from $form
group by sname;

user12171674 · Accepted Answer · 2019-10-06 09:21:08Z

0

It returns the average value of given argument, which is field name of a table. The Distinct option may be used to calculate average of only distinct values from the given set of values. If we want to find average marks from table student where marks are given in the column s_marks. For More Click

answered Oct 6, 2019 at 9:21

user12171674

1

Collectives™ on Stack Overflow

mysql sql avg function does not return correct values?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related