group function error

homer.favenir · #1 (**permalink**) 09-17-08, 04:01

hi,
can anyone please help me with my query.

Code:

SELECT
q.assessment_id,
a.idassessment,
q.professor,
a.`date`,
q.student_no,
q.last_name,
q.first_name,
a.course_code,
a.course_desc,
a.section,
case when assessment_type = 'assignment' then format(sum(q.grades)/count(a.assessment_code),2) else 0 end as 'Assignment Grade',
case when assessment_type = 'project' then format(sum(q.grades)/count(a.assessment_code),2) else 0 end as 'Project Grade'

FROM insert_grades as q
inner join upload_assessment as a
on (q.assessment_id = a.idassessment)
where q.last_name = 'cruz'
group by
a.`date`,
q.student_no,
a.section,
a.course_code,
a.course_desc,
a.assessment_type

i have a table with a assessment_type column,
there are project, assignment in assessment_type column
i want to separate them in another column
it makes another column for each assessment type but it into another NEW ROW!
it should be in 1 row.

assignment grades | project grades

i think it is in groupings, but i cant fix it.
please see attached image file
can anyone please.

thanks

r937 · #2 (**permalink**) 09-17-08, 08:25

when you are doing grouping, you cannot have the assessment_type column referenced in the CASE expression outside of the aggregate functions

change your CASE expressions to this --

Code:

, FORMAT(SUM(CASE WHEN assessment_type = 'assignment' 
                  THEN q.grades ELSE 0 END)
           / COUNT(a.assessment_code)
        ,2) AS 'Assignment Grade',
, FORMAT(SUM(CASE WHEN assessment_type = 'project' 
                  THEN q.grades ELSE 0 END)
           / COUNT(a.assessment_code)
        ,2) AS 'Project Grade'

Internet Services

Web Development

Digital Marketing

Consumer Tech