Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: group function error

    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
    Attached Thumbnails Attached Thumbnails summarygrade.bmp  
    Last edited by homer.favenir; 09-17-08 at 05:10.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •