MySQL COUNT problem

Question

Is there a way I can count how many ungraded essays there are to be displayed? IF so What else do I have to add or take away from my MySQL code?

Thanks for the help in advance!

Here is my MySQL code.

SELECT students.*, students_essays.*  
FROM students  
INNER JOIN students_essays ON students.student_id = students_essays.student_id 
LEFT JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
WHERE essays_grades.grade_id IS NULL
ORDER BY students_essays.id DESC;

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)
);

I just want to count how many ungraded essays there are total in the database. — labs
– labs, Commented Aug 6, 2010 at 23:07
Just a comment on your table structure. Is it ever possible for an essay to have more than one grade? If not, there is no reason to have the essays_grades table... you could just store the grade_id directly in the students_essays table. This will make your queries a bit easier going forward. — Mike Sherov
– Mike Sherov, Commented Aug 6, 2010 at 23:12

Martin Smith · Accepted Answer · 2010-08-06 23:11:22Z

2

As far as I can see you only need to look at 2 tables for this.

SELECT COUNT(*) 
FROM students_essays se
WHERE NOT EXISTS(SELECT * FROM 
                 essays_grades ge
                 WHERE se.id = eg.students_essays_id)

answered Aug 6, 2010 at 23:11

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Comments

wadesworld · Accepted Answer · 2010-08-06 23:06:50Z

2

SELECT count(*)  
FROM students  
INNER JOIN students_essays ON students.student_id = students_essays.student_id 
LEFT JOIN essays_grades ON students_essays.id = essays_grades.students_essays_id
WHERE essays_grades.grade_id IS NULL
ORDER BY students_essays.id DESC;

answered Aug 6, 2010 at 23:06

wadesworld

13.8k15 gold badges65 silver badges95 bronze badges

2 Comments

stakx - no longer contributing Over a year ago

IIRC, the asterisk in COUNT(*) is where one needs to pay attention: COUNT(grade_id) would not count NULL rows, while COUNT(*) does.

Tahbaza Over a year ago

+1 for the correct answer but drop the order by if the count is all that is required (wasted operation)

Collectives™ on Stack Overflow

MySQL COUNT problem

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related