[SOLVED] Question about a query involving AVG() and GROUP BY()

[mrherman]

Let's say that several schools have given the same math and reading tests.  I want to generate a single dataset that shows the average reading score and the average math score for each school.

 

Most students took both the reading test and the math test.  However, a few students took only the reading test, a few students took only the math test, and still a few other students did not take either test.

 

If a student did not take a particular test, a 0 (zero) is registered in the MySQL table of scores.

 

The query below will not work because it excludes some scores that should be counted (e.g., it excludes a reading score if a student did not take the math test, etc...)

 

select school,

      avg(readscore) as readavg,

      avg(mathscore) as mathavg

  from testtable

  where (readscore > 0 and mathscore > 0)

  group by school

 

Thanks for any help!

[cooldude832]

what you need to do is have 1 table for test scores, 1 table for test types and 1 table for schools and 1 table for students (if needed)

 

The test scores table should look like

TestingID Primary Key BigINT Auto increment

TestID Linking Key to test types table

SchoolID Linking key to schools

Score  Integer

 

Test types should be

TestID Primary key autoinc

Testname Varchar

Other descriptive fields if needed

 

Schools should be

SchoolID primary key autoinc

Schoolname varchar

Other descriptive fields if needed

 

then as you insert scores into the test scores table you can grab averages based on the student or the school or the test, but your real question was how to neglect the "0"s well if we assume everyone who took the test scores higher than a 0 then we can add to our where clause

Where readscore != '0 and mathscore !='0' 

the greater than 0 i believe is being treated as a not empty.

 

I do think if this is a serious project you should rebuild the DB in a better structure similar to what I described as the statistical analysis can be greatly improved by having it properly stored.  Such as you could compare test scores based on the time the test was taken, or using that date/time to compare outdoor weather conditions for that specific location to actual test results for students. 

 

Also mixing stuff like the school isn't a great idea cause you can link with the ID of the school for a better result.

[Barand]

try

 

SELECT school,
  AVG(IF(readscore>0, readscore, NULL)) as readavg,
  AVG(IF(mathscore>0, mathscore, NULL)) as mathavg
FROM testtable
GROUP BY school

[mrherman]

Thanks for the advice, cooldude832.  Yes, I've got to revamp the table structures of my data and I agree that this would go a long way toward solving some of my woes.  I used to use a program that allowed the extensive and easy use of CURSORs, where an almost unlimited number of "memory tables" could be generated on the fly, and then allowed to disappear when no longer needed.  MySQL is a completely different creature in many ways, and requires, as you indicated, a good deal of planning for best results.

 

------------------------------

 

Thanks, Barand.  Your suggested code worked perfectly.  I will study the "AVG(IF(....." structure and try to learn to use it.  I did spend about 90 minutes on this problem with MySQL books and internet resources at my disposal, but was unable to reach a satisfactory solution.  The closest I got was an extensive UNION join, but the math scores and reading scores were not located in the same rows as they are in your results.

[cooldude832]

the use of IF in queries is another powerful tool in MySQL so its not just AVG(IF but you can have CONCAT(IF SUM(IF  etc.  however proper table structure can avoid this.  An alternative that would aid you is to also include a bool type field in the test scores that says taken or not.  Because some kids could take the test and score 0 and you want those in you average.  It also gives you another angle to analyze data from.

[Barand]

The simple way, if you have a separate table for scores, is only add a record with score when the test is taken.

[Barand]

the use of IF in queries is another powerful tool in MySQL so its not just AVG(IF but you can have CONCAT(IF SUM(IF  etc.  however proper table structure can avoid this. 

 

You seem to be under the impression that with a proper table structure there is never a need for IF()

[cooldude832]

no, but it seems as if it can be avoided, not that it is "wrong" I personally like a stronger WHERE clause in my queries is all i'm saying.

[Barand]

So are you now implying that the IF() function and a WHERE clause are mutually exclusive?

 

[cooldude832]

So are you now implying that the IF() function and a WHERE clause are mutually exclusive?

 

 

Nope but if you want to keep shoveling words in people's mouth's cause it helps you with your superiority complex than by all means go for it. 

 

I'm only stating that their are various ways to develop a solution to a single problem.  Most mathematics and computer applications can have multiple paths to a single solution some better than other and IMHO I believe a strong where clause over conditional selection is a better solution.

[Barand]

1 ) My solution didn't use conditional selection, it merely replace 0 values with NULLs to exclude them from the aggregations.

 

2 ) What is a WHERE clause if it's not conditional selection?