Something like
SELECT uid,
AVG(number_grade) AS average_grade,
SUM(IF(letter_grade = 'A', 1, 0)) AS A_count,
SUM(IF(letter_grade = 'B+', 1, 0)) AS B+_count,
SUM(IF(letter_grade = 'B', 1, 0)) AS B_count,
SUM(IF(letter_grade = 'B-', 1, 0)) AS B-_count,
SUM(IF(letter_grade = 'C', 1, 0)) AS C_count,
FROM grades_table
GROUP BY uid;
should do.
Michael
Bob Ramsey wrote:
Hi,
I have a table of grades like this:
title, section, instructor, letter_grade, number_grade, uid
With data that would look like this:
English, 1, Smith, B, 88, 1
English, 1, Smith, B, 86, 1
English, 1, Smith, B+, 89, 1
Math, 1, Jones, A, 95, 2
Math, 1, Jones, B, 85, 2
Math, 2, Smith, C, 75, 3
Math, 2, Smith, B-, 82, 3
I want a query that will give me something like this:
uid, average_grade, A_count, B+_count, B_count, B-_count, C_count
1, 87.67, 0, 1, 2, 0, 0
2, 90, 1, 0, 1, 0, 0
3, 78.5 0, 0, 0, 1, 1
I can do this is a separate query for each grade, but that makes a lot
of little queries. Is there a way to do this in one query? Or am I
just going to have to break the average out and do the counts in one
query and the average in another?
Thanks,
bob
======================================================================
Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III
MA, Management of Information Systems 2004
MA, English Literature, 1992
ph: 1(319)335-9956 187 Boyd Law Building
fax: 1(319)335-9019 University of Iowa College of Law
mailto:[EMAIL PROTECTED] Iowa City, IA 52242-1113
For Hardware and Software questions, call 5-9124
======================================================================
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
