MySQL aggregation problems

I'm trying to count how many essays have been graded so I know how many results to display on the page. But I can't seem to get the code to work proper开发者_如何学Cly can someone help?

Thanks for the help in advance!

Here is what I got so far.

SELECT students.*, students_essays.*, COUNT(students_essays.id) 
FROM students  
INNER JOIN students_essays ON students.student_id = students_essays.student_id 
INNER JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id

It should look something like the code below for my pagination.

 $q = "SELECT COUNT(id) FROM students_essays";
 $r = mysqli_query ($mysqli, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($mysqli));
 $row = mysqli_fetch_array ($r);
 $records = $row[0];

Just in case here is my MySQL tables.

CREATE TABLE students_essays (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
content TEXT NOT NULL,
PRIMARY KEY (id)
);


CREATE TABLE students (
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_first_name VARCHAR(255) DEFAULT NULL,
student_last_name VARCHAR(255) DEFAULT NULL,
pass CHAR(40) NOT NULL,
PRIMARY KEY (student_id)
);


CREATE TABLE essays_grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade_id INT UNSIGNED NOT NULL,
students_essays_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
letter_grade VARCHAR(2) DEFAULT NULL,
grade_points FLOAT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);

Here is the error message.

 Error: 1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause in 

---

Well I am guessing here based on the information at hand...

$q = "SELECT COUNT(id) FROM students_essays se INNER JOIN essays_grades eg ON se.id = eg.students_essays_id"; 

That would return all essays with a matching grade record.

---

SELECT students.*, students_essays.*, COUNT(students_essays.id)...

students.* and students_essays.* return multiple rows but COUNT(students_essays.id) would always return just one row. In my experience, MySQL returns the number of rows and the very first row only. The simplest way to do what you want might be running two separate queries: one for count and the other for fetching actual data.