CODEWITHSUNDEEP
mysqlsql
rawrzors
rawrzors

Reputation: 167

SUM a grouped column

I am trying to sum the "Total Inservice Hours" for each user, but I am getting "Error Code: 1111. Invalid use of group function" with all my attempts. One attempt was wrapping a SUM() around the "Total Inservice Hours" field, then grouping by u.id.

Here is my SQL:

SELECT u.id,"Name",
atstat.grade,IFNULL(c.total_ceus,0) as max_ceus,c.id as courseid,atsess.id as sessid, count(c.total_ceus) as numsess, IFNULL(ROUND(((SUM(grade)/count(c.total_ceus))*total_ceus)/100,2),0) as "Total Inservice Hours"
FROM mdl_user u,mdl_course c
JOIN mdl_course_modules cm ON c.id = cm.course 
JOIN mdl_attendance_sessions atsess ON cm.instance = atsess.attendanceid
JOIN mdl_attendance_log atlog ON atlog.sessionid = atsess.id 
JOIN mdl_attendance_statuses atstat ON atstat.id = atlog.statusid 
WHERE u.id = atlog.studentid AND (u.id = 3882) AND c.total_ceus > 0
GROUP by u.id,c.id
ORDER BY u.id DESC LIMIT 300

Here is the result of that query: enter image description here

I tried to GROUP BY u.id, but I got this result: enter image description here

The only field I really care about is u.id and Total Inservice Hours. The rest are just there for debugging.

Desired output would be the sum of Total Inservice Hours, which would be 111 in this example.

Thanks!

Edit: Invalid use of group function error SQL:

SELECT u.id,"Name",
atstat.grade,IFNULL(c.total_ceus,0) as max_ceus,c.id as courseid,atsess.id as sessid, count(c.total_ceus) as numsess, SUM(IFNULL(ROUND(((SUM(grade)/count(c.total_ceus))*total_ceus)/100,2),0)) as "Total Inservice Hours"
FROM mdl_user u,mdl_course c
JOIN mdl_course_modules cm ON c.id = cm.course 
JOIN mdl_attendance_sessions atsess ON cm.instance = atsess.attendanceid
JOIN mdl_attendance_log atlog ON atlog.sessionid = atsess.id 
JOIN mdl_attendance_statuses atstat ON atstat.id = atlog.statusid 
WHERE u.id = atlog.studentid AND (u.id = 3882) AND c.total_ceus > 0
GROUP by u.id
ORDER BY u.id DESC LIMIT 300

Upvotes: 0

Views: 110

Answers (1)

Barmar
Barmar

Reputation: 780724

You can't wrap one aggregate function around another.

You need to put your original query in a subquery, in order to sum one of its columns. And since you're only interested in the ID and Total Inservice Hours, you can leave out the other SELECT columns.

SELECT id, SUM(`Total Inservice Hours`) AS "Total Inservice Hours"
FROM (SELECT u.id, IFNULL(ROUND(((SUM(grade)/count(c.total_ceus))*total_ceus)/100,2),0) as "Total Inservice Hours"
    FROM mdl_user u,mdl_course c
    JOIN mdl_course_modules cm ON c.id = cm.course 
    JOIN mdl_attendance_sessions atsess ON cm.instance = atsess.attendanceid
    JOIN mdl_attendance_log atlog ON atlog.sessionid = atsess.id 
    JOIN mdl_attendance_statuses atstat ON atstat.id = atlog.statusid 
    WHERE u.id = atlog.studentid AND (u.id = 3882) AND c.total_ceus > 0
    GROUP by u.id,c.id
    ORDER BY u.id DESC LIMIT 300) AS subq
GROUP BY id

Upvotes: 2

Related Questions