Jump to content
parkerj

IFNULL Return Zero When GROUP BY

Recommended Posts

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.

 

avg-drops.png

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.