[RESOLVED] Help with SQL statement

janhouse00

Hello, I have a table that contains the examination results of each student as follow:

id student_id subject_id grade

1 1023 1 B
2 1023 2 A
3 1023 3 A
4 1023 4 C
5 1024 1 A
6 1024 2 A
7 1024 3 B
8 1024 4 A
9 1025 1 D
...
..

I wonder if I can use the SQL query to return the results something like:

student_id subject_id grade number_of_A

1023 1 B 2
1023 2 A 2
1023 3 A 2
1023 4 C 2
1024 5 A 3
1024 6 A 3
1024 7 B 3
1024 8 A 3

The trickiest part is to find the number of A's got by each student while returning every single row of record. It doesn't matter if I got duplicated entries since I can filter them in PHP afterwards. BTW, my database doesn't support subquery right now.

Many thanks.

coreyp_1

been a while since I worked without subqueries, but how about this:

SELECT s.student_id s.subject_id s.grade COUNT(x.grade) AS number_of_A
FROM student_table s, student_table x
WHERE s.student_id = x.student_id AND x.grade = 'A'
ORDER BY s.student_id

janhouse00

Thanks for the prompt reply,
after applying the code, MySQL said:
[localhost] ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

so I added the GROUP BY clause into it:

SELECT s.student_id, s.subject_id, s.grade, COUNT(x.grade) AS number_of_A
FROM exam_result s, exam_result x
WHERE s.student_id = x.student_id AND x.grade = 'A'
GROUP BY s.student_id
ORDER BY s.student_id

And it returned:
+--------+------------+-------+-------------+
| student_id | subject_id | grade | number_of_A |
+--------+------------+-------+-------------+
| 1 | 1 | A | 81 |
| 567 | 1 | A | 100 |
| 651 | 1 | A | 81 |
| 652 | 1 | A | 100 |
| 653 | 1 | A | 100 |
| 654 | 1 | A | 81 |
+--------+------------+-------+-------------+

And I realized that I shouldn't use "GROUP BY" here because it didn't really return every single row of record. Each student seemed to have only 1 record and the subject_id was always the first one. I guess "COUNT" works closely with "GROUP BY" right? Did I miss some points?

coreyp_1

how about

 SELECT s.student_id, s.subject_id, s.grade, COUNT(x.grade) AS number_of_A
FROM exam_result s, exam_result x
WHERE s.student_id = x.student_id AND x.grade = 'A'
GROUP BY x.student_id
ORDER BY s.student_id

janhouse00

Sorry it still gave me only one record of each student.

Maybe I can't do everything in one go. Now I decide to use two seperated queries. One to fetch all the records, another is to COUNT the number of A's GROUP BY each student, then only I filter the junk records in PHP array.

coreyp_1

sorry, but my ideas are exhausted. I know its possible with subqueries, but can't figure out a single, non-subquery alternative.

janhouse00

It's ok. I greatly appreciate your helps anyway. Again thank you very much

coreyp_1

OK, I'm stubborn. one more try:

SELECT s.student_id, s.subject_id, s.grade, COUNT(x.grade) AS number_of_A
FROM exam_result s
LEFT JOIN exam_result x USING (student_id, subject_id)
WHERE x.grade = 'A'
GROUP BY x.student_id
ORDER BY s.student_id

janhouse00

Ermm, mind if I upload the table structure and its data anyway? I've had them all in a zip file. I still can't get it worked.

harmor

Here's the query I made

$getresults = mysql_query("SELECT * FROM exam_result WHERE student_id='678' && grade='A' ")or die(mysql_error());
$exam = mysql_num_rows($getresults);

   echo $exam;

?>

what it's doing is getting every row where the student_id is 678 and where the grade is A.

To get any student_id and grade you'll have to pass them in the address bar.

yoursite.com/exams_results.php?student_id=678&grade=B

(I am re-rewritring the query I showed you above)

$getresults = mysql_query("SELECT * FROM exam_result WHERE student_id='$_GET[student_id]' && grade='".$_GET['grade']."  ' ")or die(mysql_error());
$exam = mysql_num_rows($getresults);

   echo $exam;

?>

Edit:
I wasn't thinking when I made this. I'll try to code it the way you described it.
Sorry

janhouse00

Sorry for confusing you, from that table I will eventually generate an examination report in HTML table, which contains the overall academic performance of each student.

jazz_snob

I think its easiest to get all the rows you want, then iterate over each student id and add up all the A's, B's, etc for that student. Actually, I'm not entirely sure I understand what you are after.

Otherwise, to try and get a query that returns what you want, I made a terrible cludge using a temporary table that is SLOW:

create temporary table tmp(id int, grade char(1), sum int );
insert into tmp select student_id as id, grade, count(grade) from exam_result group by grade;
-- did limit just to return a few rows
select tmp.grade, sum, student_id, subject_id from tmp left join exam_result on student_id=tmp.id order by student_id, subject_id limit 50;
-- press return, then wait 5 seconds for results!
-- get something like this


grade sum student_id subject_id
A        9      1                 1
A        9      1                 2
A        9      1                 3

...

drop the tmp table when you are done.

janhouse00

jazz_snob wrote:
I think its easiest to get all the rows you want, then iterate over each student id and add up all the A's, B's, etc for that student.

This was exactly the first idea came to my mind. But when I was reminded that my script should be able to perform sorting on the column number of A's as well, which I think it's a bit hard to implement with that way, I just started to find out an alternative solution. Now I've solved the problem by making 2 queries and does all the nasty jobs in PHP array. Thanks though.