MYSQL SUM Query With Group By and Join Issue

Question

I am using MYSQL with PHP to do the server side scripting. I am having trouble trying to build a query that aggregates some review data that needs to be joined with some information about who the review is for.

I have two tables. One called "schools" with general information about a school and another called "reviews" with the user supplied review information.

The school table is structured as:

id, schoolName, schoolCity, schoolCountry (for the field names)

With example return data as:

1, ASD, Dubai, UAE
2, DAA, Dubai, UAE

etc.

The "reviews" table has 57 columns:

RID, schoolID, profileID, dateCreated, Q1, Q2, Q3,...Q51, Q52, freeReview (as field names)

with data returned like:

1, 8, 55, 2012-10-10 05:16:05, 10, 8, 9, 2, 3....

From Q1 to Q25, these are questions within a review form that are returned as integers between 0-10. The other columns tie to a profile table and the school table (as above).

I am able to create a query for an individual school that SUMS all of the reviews for the given school and generates an average score for the school based on the number of reviews submitted for that school.

What I am having trouble with is I would like to create a page that lists all (broken up by pagination) the schools in a table with that averaged and SUM-ed score.

This is the query I have that generates the overall average score for a school.

$singlereviewtotal = mysql_query("
SELECT ROUND(
((SUM(Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10+Q11+Q12+Q13+Q14+Q15+
Q16+Q17+Q18+Q19+Q20+Q21+Q22+Q23+Q24+Q25+Q26+Q27+Q28+Q29+Q30+
Q31+Q32+Q33+Q34+Q35+Q36+Q37+Q38+Q39+Q40+Q41+Q42+Q43+Q44+Q45+
Q46+Q47+Q48+Q49+Q50+Q51+Q52)/((52*10)*
(SELECT COUNT(*) FROM reviews WHERE schoolID='$schoolvalue')))*10), 2)
AS total
FROM reviews
WHERE schoolID='$schoolvalue'") or die(mysql_error());

This generates the correct summed and averaged value of all of the columns and rows, which is rounded to 2 decimals. The WHERE clause comes from a PHP value in the page that tells the query which school ID to use.

This is my attempt to try to get all of the schools into a list.

$sumreviews = mysql_query("SELECT *, 
 ROUND(
 ((Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10+Q11+Q12+Q13+Q14+Q15+
 Q16+Q17+Q18+Q19+Q20+Q21+Q22+Q23+Q24+Q25+Q26+Q27+Q28+Q29+Q30+
 Q31+Q32+Q33+Q34+Q35+Q36+Q37+Q38+Q39+Q40+Q41+Q42+Q43+Q44+Q45+
 Q46+Q47+Q48+Q49+Q50+Q51+Q52)/(52*10)
 *10),2) AS atotal 
 FROM reviews
 RIGHT JOIN schools ON schools.SID = reviews.schoolID
 GROUP BY schools.schoolName, reviews.schoolID
 ORDER BY atotal DESC")or die(mysql_error());

This query gives me the list of schools, yet the output for "atotal" only gives me the SUM of the first row for that given school (which in this case is 7.31 - and can be seen just below in the output, but the averaged score should be 7.23).

If I remove the GROUP BY in the query above I get the list of all of the schools, plus extra rows for some schools who have more reviews - like American School of Dubai.

School Name, City, Country, Rating
American School of Dubai, Dubai, U.A.E. 7.31
Dubai American Academy, Dubai, U.A.E. 7.19
American School of Dubai, Dubai, U.A.E. 7.15
International School of Bangkok, Bankok, Thailand
American School of Rio de Janeiro, Rio, Brazil

American School of Budapest, Budapest, Hungary

Zurich International School, Zurich, Switzerland

I am wondering, how to construct the query so that the "AS atotal" rows for all the given schools are grouped and averaged? If I averaged the 7.31 with the 7.15 listed as individual review scores for the American School of Dubai, I would have the correct 7.23. The answer is just right there, but I can't seem to connect it together.

I apologize in advance for my lack of question "styling"!

Just a random thought: make sure none of your Q fields are NULL and are defaulting to 0. All the best. — Bryan Allo
– Bryan Allo, Commented Oct 31, 2012 at 18:01

LSerni · Accepted Answer · 2012-10-31 17:53:07Z

1

Have you tried something like

 SELECT schools.schoolName,
 ROUND(AVG(
 ((Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10+Q11+Q12+Q13+Q14+Q15+
 Q16+Q17+Q18+Q19+Q20+Q21+Q22+Q23+Q24+Q25+Q26+Q27+Q28+Q29+Q30+
 Q31+Q32+Q33+Q34+Q35+Q36+Q37+Q38+Q39+Q40+Q41+Q42+Q43+Q44+Q45+
 Q46+Q47+Q48+Q49+Q50+Q51+Q52)/(52*10)
 *10)),2) AS average
 FROM reviews
 RIGHT JOIN schools ON (schools.SID = reviews.schoolID)
 GROUP BY schools.SID
 ORDER BY average DESC;

Your last query does not feature any aggregate function, so that might be the reason why it didn't return the expected results.

Haven't tested the query, but if you supplied a SQLFiddle or a sample test data I could verify.

answered Oct 31, 2012 at 17:53

LSerni

57.7k10 gold badges70 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1557966 Over a year ago

This worked like a charm, thanks so much! I didn't realize (even from searching) that AVG works vertically as well as horizontally across rows and columns. Thanks so much!

fthiella · Accepted Answer · 2012-10-31 18:07:36Z

1

I didn't test the query, but i think it's better to write first one as this:

SELECT ROUND(AVG((Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10+Q11+Q12+Q13+Q14+Q15+
    Q16+Q17+Q18+Q19+Q20+Q21+Q22+Q23+Q24+Q25+Q26+Q27+Q28+Q29+Q30+
    Q31+Q32+Q33+Q34+Q35+Q36+Q37+Q38+Q39+Q40+Q41+Q42+Q43+Q44+Q45+
    Q46+Q47+Q48+Q49+Q50+Q51+Q52)/52),2) AS total
FROM reviews
WHERE schoolID='$schoolvalue'

it's easier to write and to read.

For the second query, the problem is that there's no aggregate function AVG on the SELEC clause. Take a look at Iserni answer, i think it's correct.

answered Oct 31, 2012 at 18:07

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

1 Comment

user1557966 Over a year ago

I marked this answer as correct as well because it cleaned up the original query! Thanks so much.

Collectives™ on Stack Overflow

MYSQL SUM Query With Group By and Join Issue

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related