IFNULL Return Zero When GROUP BY

[NomadicJosh]

I've created a course utilization report which is made up of several different MySQL queries. As you know, if a row contains no data, the result is NULL. IFNULL works to combat that, but doesn't work if there is a GROUP BY clause (GROUP BY a.courseSection). So, I need to figure out how to get it to return zero when one of the rows is null. If you look at the line Average Student Drops in the screenshot, you see that there are two cells missing which is throwing off the data. For example, since 2015 is null, everything shifts to the left. Below is the query I am using; any help with this is greatly appreciated.

 

SELECT AVG(drops)
FROM (
	SELECT IFNULL(COUNT(a.stuAcadCredID),0) as drops,YEAR(a.addDate) as year
	FROM stu_acad_cred a
	LEFT JOIN course_sec b ON a.courseSection = b.courseSection
	LEFT JOIN course c ON b.courseID = c.courseID
	WHERE a.status IN('D')
	GROUP BY a.courseSection
) stuDrops 
	GROUP BY year
	DESC LIMIT 10

[Barand]

What happens if you move the IFNULL() from the COUNT() to AVG(drops)

SELECT IFNULL(AVG(drops), 0) 
....

Do you need the course and course_section tables in that query? They look redundant.

[NomadicJosh]

What happens if you move the IFNULL() from the COUNT() to AVG(drops)

SELECT IFNULL(AVG(drops), 0) 
....

Do you need the course and course_section tables in that query? They look redundant.

Placing IFNULL around AVG will cause all the results to become zero. Yes, those tables are needed for dynamic filtering.

[NomadicJosh]

Since student drops may not be a consistent data element and the where clause is defined, I think it is best to just remove it because there is no work around for the query as is.

[NomadicJosh]

The answer hit me smack over the head one night. I can do the following query and leave out the WHERE clause:

SELECT drops
			FROM (
			    SELECT IFNULL(COUNT(a.status)/SUM(CASE a.status WHEN 'D' THEN 1 ELSE 0 END),0) as drops,YEAR(a.addDate) as year
			    FROM stu_acad_cred a
			    LEFT JOIN course_sec b ON a.courseSection = b.courseSection
			    LEFT JOIN course c ON b.courseID = c.courseID
			    GROUP BY year
			    ) stuDrops 
			GROUP BY year
			DESC LIMIT 10