PHP - Building a Learderboard from MySQL data

Question

I have two sets (users and their tests data) of data from MySQL. I'm going to build a leaderboard from these data in such a way where every user should have an average grade (%), scores (sum of all tests results) and total number of tests that user attended. At the end, when all data is formed for the leaderboard it should be sorted by descending, so first highest average grade and scores with associated user etc. I started with while-loop, but stacked with linking the data into user names.

PHP code:

// contains set of data with fields user_id(INT) and name(VARCHAR)
$users = mysqli_fetch_assoc($result_users);

// contains set of data with fields user_id(INT), socres(DECIMAL), passed(BOOL)
$data = mysqli_fetch_assoc($result_data);

$pos = null; // collect positive tests
$neg = null; // collect negative tests
while ($data = mysqli_fetch_assoc($result_all)) {
    if ($data['quiz_passed'] == 1) {
        $pos += 1;
    } elseif ($data['quiz_passed'] == 0) {
        $neg += 1;
    }
}

Expected Leaderboard Results:

Name     Average (Pos/Neg)     Scores (Sum of scores field)     Total tests (Pos+Neg)
-------------------------------------------------------------------------------------
John     80%                   143                              9

         // 4 Pos / 5 Neg                                       // 4 Pos + 5 Neg

Any help would be appreciated.

UPDATES:

User Table
----------
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL,        // PRIMARY KEY
  `name` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `fullname` varchar(255) NOT NULL,
  `token` varchar(128) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(4, 'test1', 'password_here1', 'Tim Roth', 'token_here1'),
(5, 'test2', 'password_here2', 'Christoph Waltz', 'token_here2'),
(6, 'test3', 'password_here3', 'John Travolta', 'token_here3'),


Data Table
----------
CREATE TABLE IF NOT EXISTS `data` (
  `id` int(11) NOT NULL,            // PRIMARY KEY
  `user_id` int(11) NOT NULL,
  `scores` decimal(3,2) NOT NULL,
  `passed` tinyint(1) NOT NULL,
  `time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(1, 6, '0.60', 0, '2014-11-12 01:24:43'),
(2, 4, '0.75', 1, '2014-10-31 06:33:48'),
(3, 4, '0.90', 1, '2014-11-02 15:11:09'),
(4, 4, '0.50', 0, '2014-11-06 19:29:19'),
(5, 5, '0.75', 1, '2014-11-07 08:21:44'),
(6, 5, '0.60', 0, '2014-11-10 17:34:00'),
(7, 6, '0.60', 0, '2014-11-11 16:13:50'),
(8, 4, '0.85', 1, '2014-11-12 13:22:49')

Answer 1

Based on the data, there is one minor issue. Your calculation for average appears to be the count of where pass = 1 divided by where pass = 0. So if someone hasn't failed an exam there will be a divide by zero. While easy enough to code around I would need to know what you want to do in this situation.

SELECT u.user_id, SUM(d.passed) / SUM(IF(d.passed = 0, 1, 0)) AS `Average`, SUM(d.scores) AS `Scores`, COUNT(d.id) AS `Total Tests`
FROM user u
INNER JOIN data d
ON u.user_id = d.user_id
GROUP BY u.user_id;

Not sure if it would be more appropriate to count the number of passes by the number of exams.

SELECT u.user_id, SUM(d.passed) / COUNT(d.passed) AS `Average`, SUM(d.scores) AS `Scores`, COUNT(d.id) AS `Total Tests`
FROM user u
INNER JOIN data d
ON u.user_id = d.user_id
GROUP BY u.user_id;

SQL fiddle to show the 2 results:-

http://www.sqlfiddle.com/#!2/ecf3d2/1

Answer 2

SELECT
    ROUND(SUM(d.passed) / COUNT(*) * 100, 2) AS average,
    SUM(d.score) AS score,
    COUNT(*) AS total
FROM user AS u
INNER JOIN data AS d ON d.user_id = u.id
GROUP BY u.id