How to use aggregate functions in mysql?

Question

I have 2 tables

1. students
2. payments

In students table I have fields like

student_id(Primary key),
student_name, 
student_university, 
student_counselor

In payments table I have fields like

payment_id(Primary key),
student_id(Foreign key reference to students table), 
payable,
paid, 
balance

I am trying to achive:

• Display the result in the form below i.e each counselor has their students in different university.
• The total number of students belongs to particular university from particular counselor and how much amount is payable, paid and balance as shown in the table above.

Any insight?

Answer 1

Create statement for student table:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `university` varchar(45) DEFAULT NULL,
  `counselor` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Create statement payment table:

CREATE TABLE `payment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `payable` int(11) NOT NULL DEFAULT '0',
  `paid` int(11) NOT NULL DEFAULT '0',
  `balance` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK payment student_id student id_idx` (`student_id`),
  CONSTRAINT `FK payment student_id student id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

Students and payments data insertion

INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 1', 'u 1', 'c 1');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 2', 'u 1', 'c 1');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 3', 'u 1', 'c 2');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 4', 'u 1', 'c 2');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 5', 'u 2', 'c 3');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 6', 'u 2', 'c 3');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 7', 'u 2', 'c 4');
INSERT INTO `student` (`name`, `university`, `counselor`) VALUES ('student 8', 'u 2', 'c 4');
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('1', 500, 300, 200);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('2', 500, 300, 200);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('3', 400, 400, 400);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('4', 400, 400, 400);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('5', 100, 200, 300);
INSERT INTO `payment` (`student_id`, `payable`, `paid`, `balance`) VALUES ('6', 51, 52, 53);

And here what we have:

mysql> select * from student;
+----+-----------+------------+-----------+
| id | name      | university | counselor |
+----+-----------+------------+-----------+
|  1 | student 1 | u 1        | c 1       |
|  2 | student 2 | u 1        | c 1       |
|  3 | student 3 | u 1        | c 2       |
|  4 | student 4 | u 1        | c 2       |
|  5 | student 5 | u 2        | c 3       |
|  6 | student 6 | u 2        | c 3       |
|  7 | student 7 | u 2        | c 4       |
|  8 | student 8 | u 2        | c 4       |
+----+-----------+------------+-----------+
8 rows in set (0.02 sec)


mysql> select * from payment;
+----+------------+---------+------+---------+
| id | student_id | payable | paid | balance |
+----+------------+---------+------+---------+
|  1 |          1 |     500 |  300 |     200 |
|  2 |          2 |     500 |  300 |     200 |
|  3 |          3 |     400 |  400 |     400 |
|  4 |          4 |     400 |  400 |     400 |
|  5 |          5 |     100 |  200 |     300 |
|  6 |          6 |      51 |   52 |      53 |
+----+------------+---------+------+---------+
6 rows in set (0.00 sec)

query itself to select all counselor:

SELECT 
    counselor,
    university,
    COUNT(name) AS 'no of students',
    SUM(payment.payable) AS payable,
    SUM(payment.paid) AS paid,
    SUM(payment.balance) AS balance
FROM
    student
        LEFT JOIN
    payment ON payment.student_id = student.id
GROUP BY counselor;

result:

mysql>     SELECT
    ->         counselor,
    ->         university,
    ->         COUNT(name) AS 'no of students',
    ->         SUM(payment.payable) AS payable,
    ->         SUM(payment.paid) AS paid,
    ->         SUM(payment.balance) AS balance
    ->     FROM
    ->         student
    ->             LEFT JOIN
    ->         payment ON payment.student_id = student.id
    ->     GROUP BY counselor;
+-----------+------------+----------------+---------+------+---------+
| counselor | university | no of students | payable | paid | balance |
+-----------+------------+----------------+---------+------+---------+
| c 1       | u 1        |              2 |    1000 |  600 |     400 |
| c 2       | u 1        |              2 |     800 |  800 |     800 |
| c 3       | u 2        |              2 |     151 |  252 |     353 |
| c 4       | u 2        |              2 |    NULL | NULL |    NULL |
+-----------+------------+----------------+---------+------+---------+

Also you don't have to use student prefix on all fields in student table. It's obvious that name in student table means student name and so on.

And here is simply php code to print table.

This is db connect class so you can skip it:

define('DB_MAIN', 'localhost|someroot|some|db');

class my_db{

    private static $databases;
    private $connection;

    public function __construct($connDetails){
        if(!is_object(self::$databases[$connDetails])){
            list($host, $user, $pass, $dbname) = explode('|', $connDetails);
            $dsn = "mysql:host=$host;dbname=$dbname";
            self::$databases[$connDetails] = new PDO($dsn, $user, $pass);
        }
        $this->connection = self::$databases[$connDetails];
    }

    public function fetchAll($sql){
        $args = func_get_args();
        array_shift($args);
        $statement = $this->connection->prepare($sql);
        $statement->execute($args);
         return $statement->fetchAll(PDO::FETCH_OBJ);
    }
}


$db = new my_db(DB_MAIN);

main php code itself:

$universities = $db->fetchAll('SELECT distinct university FROM student');
$counselors = $db->fetchAll('SELECT distinct counselor FROM student');
$payments_ = $db->fetchAll(' SELECT
    counselor,
    university,
    COUNT(name) AS \'no of students\',
    SUM(payment.payable) AS payable,
    SUM(payment.paid) AS paid,
    SUM(payment.balance) AS balance
FROM
    student
        LEFT JOIN
    payment ON payment.student_id = student.id
GROUP BY counselor;');

$payments = [];

foreach ($payments_ as $payment)
    $payments[$payment->counselor][$payment->university] = $payment;
?>



<table border="1">
    <tr>
        <td rowspan="2" >counselor</td>
<?php
    foreach ( $universities as $key => $university){ ?>

        <td colspan="4" ><?=$university->university ?> </td>
    <?php } ?>
    </tr>
    <tr>
    <?php foreach ( $universities as $university){?>
        <td>no of students</td>
        <td>payable</td>
        <td>paid</td>
        <td>balance</td>
    <?php } ?>
    </tr>
    <tr>
    <?php foreach ( $counselors as $counselor){?>
    <?php foreach ( $universities as $key => $university){
        $payment = $payments[$counselor->counselor][$university->university];
    ?>
        <?php if(!$key){?>
        <td><?=$counselor->counselor?></td>
        <?php } ?>
        <td><?=(int)$payment->{'no of students'}?></td>
        <td><?=number_format($payment->payable,0,',','')?></td>
        <td><?=number_format($payment->paid,0,',','')?></td>
        <td><?=number_format($payment->balance,0,',','')?></td>
    <?php } ?>
    </tr>
    <?php } ?>
</table>

html result

<table border="1">
  <tr>
    <td rowspan="2">counselor</td>

    <td colspan="4">u 1 </td>

    <td colspan="4">u 2 </td>
  </tr>
  <tr>
    <td>no of students</td>
    <td>payable</td>
    <td>paid</td>
    <td>balance</td>
    <td>no of students</td>
    <td>payable</td>
    <td>paid</td>
    <td>balance</td>
  </tr>
  <tr>
    <td>c 1</td>
    <td>2</td>
    <td>1000</td>
    <td>600</td>
    <td>400</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>c 2</td>
    <td>2</td>
    <td>800</td>
    <td>800</td>
    <td>800</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>c 3</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>2</td>
    <td>151</td>
    <td>252</td>
    <td>353</td>
  </tr>
  <tr>
    <td>c 4</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
    <td>2</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
</table>