How to calculate average of a SQL Column in php by Group Id and display on HTML Page?

Question

I want to calculate the Average value of all other columns grouping them by collegename. i.e RVCE with Average of all other columns, BMS with Average of all other columns and so on and display the value in HTML.

SQL Database Structure Image

I have tried the following code in but it is returning me no values.

<?php
$hostname="localhost:8889";
$username="root";
$password="root";
$db = "rank";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT collegename,AVG(campus)  
FROM college       
GROUP BY collegename') as $row) {
echo "<tr>";
echo "<td>" . $row['AVG(campus)'] . "</td>";
echo "</tr>";
}
?>

I got the error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use nea 'SELECT collegename AVG(campus) AS avg_campus FROM college GROUP BY collegename' at line 2 — Siddhartha B, Aug 17 '17 at 20:41
Can you recreate the error in SQLFiddle or in rextester? The syntax is correct, there must be something else going on... — Eli, Aug 17 '17 at 21:07
Silly question - are you running in mySQL with the single quotes? — Eli, Aug 17 '17 at 21:10
Yes it Words on SQLFiddle http://sqlfiddle.com/#!9/6abec/3 and What if i want to do for each column? — Siddhartha B, Aug 17 '17 at 21:23

Dalin Huang · Accepted Answer · 2017-08-18T13:29:19.633

1

Try this one?

<?php
$hostname="127.0.0.1";
$port=8889;
$username="root";
$password="root";
$db = "rank";
$dbh = new PDO("mysql:dbname=$db;host=$hostname;port=$port", $username, $password);
foreach($dbh->query('SELECT collegename,AVG(campus) AS avg_campus  
FROM college       
GROUP BY collegename') as $row) {
echo "<tr>";
echo "<td>" . $row['collegename'] . " AVG: " . $row['avg_campus'] . "</td>";
echo "</tr>";
}
?>

edited Aug 18 '17 at 13:29

answered Aug 17 '17 at 19:48

Dalin Huang

11,212
5
32
49

@SiD even `collegename` is blank? – Dalin Huang Aug 17 '17 at 20:10
Yes, Nothing is displayed – Siddhartha B Aug 17 '17 at 20:57
@SiddharthaB ok try the updated one, added port and using `127.0.0.1` as host (should be the same as local host), also `mysql` using lowercase – Dalin Huang Aug 18 '17 at 13:31

shabang · Answer 2 · 2017-08-18T12:19:03.950

0

SELECT
     AVG((campus + location + cost + internet + safety + placement + hapiness + weather + fun + fests)/10) as avg_campus,
   FROM
     college
   GROUP BY
     collegename

If you have only one row for a college name, then the group by is useless and... the AVG() as well

For average of each individual column :

SELECT AVG(campus) as campus, AVG(location) as location, AVG(cost) as cost, AVG(internet) as internet, AVG(safety) as safety, AVG(placement) as placement, AVG(hapiness) as hapiness, AVG(weather) as weather, AVG(fun) as fun, AVG(fests) as fests
FROM college 
GROUP BY collegename

edited Aug 18 '17 at 12:19

answered Aug 17 '17 at 19:57

shabang

160
1
13

this https://stackoverflow.com/questions/14787561/find-the-average-of-two-combined-columns-in-sql can be usefull for you to read – shabang Aug 17 '17 at 19:58
I want to calculate average of each individual column not a combined value – Siddhartha B Aug 17 '17 at 20:09
Then for average of each individual column : `SELECT AVG(campus) as campus, AVG(location) as location, AVG(cost) as cost, AVG(internet) as internet, AVG(safety) as safety, AVG(placement) as placement, AVG(hapiness) as hapiness, AVG(weather) as weather, AVG(fun) as fun, AVG(fests) as fests FROM college GROUP BY collegename` – shabang Aug 18 '17 at 12:16

How to calculate average of a SQL Column in php by Group Id and display on HTML Page?

2 Answers2