Search the Community
Showing results for tags 'ifnull'.
Found 2 results
-
Hi All, I have the following, and when i run it the ifnull() is returning null rather than 0 as shown in the attached. Any help on this would be greatly appreciated. select * from ( SELECT ptsl_ptd_id, SUBSTRING(ptsl_date,1,10) as ptsl_date, ptsl_z_id, z_rfid, ptsl_limit FROM `prs_ptsl` inner join prs_z on ptsl_z_id=z_id where ptsl_ptd_id='7' ) as limits left join ( SELECT pr_ptd_id, za_sdate, za_z_id, za_z_rfid, IFNULL(count(za_pr_id), 0) as used FROM `prs_za` inner join prs_pr on za_pr_id=pr_id where prs_pr.pr_status = 'Approved' or prs_pr.pr_status = 'Submitted' group by za_sdate, za_z_id, za_z_rfid ) as used on limits.ptsl_ptd_id=used.pr_ptd_id and limits.ptsl_date=used.za_sdate and ptsl_z_id=za_z_id where ptsl_date = '2021-06-12' and (ptsl_limit - IFNULL(used, 0) >= 0) limit 100
-
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
