# Forcing a 0 if the parameter does not come up in results **URL:** \ **Category:** Ignition\ **Tags:** database\ **Created:** [July 1, 2025, 8:51pm UTC](https://forum.inductiveautomation.com/t/forcing-a-0-if-the-parameter-does-not-come-up-in-results/108012 "2025-07-01T20:51:46Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![crumberger](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/crumberger/32/79956_2.png) [@crumberger](https://forum.inductiveautomation.com/u/crumberger)\ **Post date:** [July 1, 2025, 8:51pm UTC](https://forum.inductiveautomation.com/t/forcing-a-0-if-the-parameter-does-not-come-up-in-results/108012/1 "2025-07-01T20:51:46Z")
I have a query where I'm looking for the Top 5 results of downtime over a 4-week period in our database, and getting the information from each of those 5 categories for each of those 4 weeks. The problem is that some of those weeks may not have any results for one of those 5 categories. How do I make the query return a result where it includes the parameter that had no result and then the quantity for the correlating column be 0? And before you ask, the reason I need it this way is because it's going in a stacked bar chart, and without the 0 value, it will create gaps between the stacked bars. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/7/d79477f4785c9813b2145e57f745256cbc2850b4.png) ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/2/92b6202f766bf509582b29162ef4a7eba383acce.png) ```python SELECT dispatchl2ldb.dispatchType, dispatchl2ldb.reasonDesc, IF(dispatchl2ldb.reasonDesc = :topCat1,1, IF(dispatchl2ldb.reasonDesc = :topCat2,2, IF(dispatchl2ldb.reasonDesc = :topCat3,3, IF(dispatchl2ldb.reasonDesc = :topCat4,4, IF(dispatchl2ldb.reasonDesc = :topCat5,5,0))))) AS catOrder, SUM(dispatchl2ldb.minsDown) AS minTotal, WEEKOFYEAR(dispatchl2ldb.reported) AS Week FROM dispatchl2ldb WHERE dispatchl2ldb.reasonDesc = :topCat1 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Waiting On" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat2 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Waiting On" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat3 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Waiting On" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat4 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Waiting On" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat5 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Waiting On" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat1 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Operational Fix" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat2 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Operational Fix" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat3 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Operational Fix" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat4 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Operational Fix" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat5 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "Operational Fix" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat1 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "FDT" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat2 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "FDT" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat3 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "FDT" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat4 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "FDT" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week OR dispatchl2ldb.reasonDesc = :topCat5 AND dispatchl2ldb.line = :line AND dispatchl2ldb.shiftID = :shiftID AND dispatchl2ldb.dispatchType = "FDT" AND WEEKOFYEAR(dispatchl2ldb.reported) = :week GROUP BY reasonDesc, Week ORDER BY catOrder ASC ``` ---
**Author:** ![Ryan\_Deardorff](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan_deardorff/32/63815_2.png) [@Ryan\_Deardorff](https://forum.inductiveautomation.com/u/Ryan_Deardorff)\ **Post date:** [July 1, 2025, 9:08pm UTC](https://forum.inductiveautomation.com/t/forcing-a-0-if-the-parameter-does-not-come-up-in-results/108012/2 "2025-07-01T21:08:43Z")
While I don't know the solution to your problem, I can tell that you have a lot of redundancy in that query. It can be simplified to: ```python SELECT d.dispatchType, d.reasonDesc, CASE d.reasonDesc WHEN :topCat1 THEN 1 WHEN :topCat2 THEN 2 WHEN :topCat3 THEN 3 WHEN :topCat4 THEN 4 WHEN :topCat5 THEN 5 ELSE 0 END AS catOrder, SUM(d.minsDown) AS minTotal, WEEKOFYEAR(d.reported) AS Week FROM dispatchl2ldb d WHERE d.reasonDesc IN (:topCat1, :topCat2, :topCat3, :topCat4, :topCat5) AND d.line = :line AND d.shiftID = :shiftID AND d.dispatchType IN ('Waiting On', 'Operational Fix', 'FDT') AND WEEKOFYEAR(d.reported) = :week GROUP BY d.reasonDesc, Week, d.dispatchType ORDER BY catOrder ASC; ``` ---
**Author:** ![Leor\_Fishman](https://avatars.discourse-cdn.com/v4/letter/l/e19adc/32.png) [@Leor\_Fishman](https://forum.inductiveautomation.com/u/Leor_Fishman)\ **Post date:** [July 1, 2025, 11:49pm UTC](https://forum.inductiveautomation.com/t/forcing-a-0-if-the-parameter-does-not-come-up-in-results/108012/3 "2025-07-01T23:49:18Z")
i don't know the exact solution here, but you may want to look into > **[W3Schools.com](https://www.w3schools.com/sql/sql_join_left.asp)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. and [SQL Server COALESCE() Function](https://www.w3schools.com/sql/func_sqlserver_coalesce.asp) ---
**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\ **Post date:** [July 3, 2025, 7:16pm UTC](https://forum.inductiveautomation.com/t/forcing-a-0-if-the-parameter-does-not-come-up-in-results/108012/4 "2025-07-03T19:16:01Z")
How does data get into the DB? It may be easier to force insert a "0" row at the end of each day. Or make a second, simple, query that you run before this one and if it returns nothing then insert a "0" row. ---
**Author:** ![crumberger](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/crumberger/32/79956_2.png) [@crumberger](https://forum.inductiveautomation.com/u/crumberger)\ **Post date:** [July 3, 2025, 7:23pm UTC](https://forum.inductiveautomation.com/t/forcing-a-0-if-the-parameter-does-not-come-up-in-results/108012/5 "2025-07-03T19:23:01Z")
We ended up finding a way to make it work, and rewrote it to put the week totals in one table. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/2/d2e91c2a6e6ed854423b6a9189790dbd4452660e.png) ```python SELECT d.reasonDesc, SUM(CASE WEEKOFYEAR(d.reported) WHEN :week THEN d.minsDown ELSE 0 END) AS wC, SUM(CASE WEEKOFYEAR(d.reported) WHEN ((:week)-1) THEN d.minsDown ELSE 0 END) AS w1, SUM(CASE WEEKOFYEAR(d.reported) WHEN ((:week)-2) THEN d.minsDown ELSE 0 END) AS w2, SUM(CASE WEEKOFYEAR(d.reported) WHEN ((:week)-3) THEN d.minsDown ELSE 0 END) AS w3, CASE d.reasonDesc WHEN :topCat1 THEN 1 WHEN :topCat2 THEN 2 WHEN :topCat3 THEN 3 WHEN :topCat4 THEN 4 WHEN :topCat5 THEN 5 ELSE 0 END AS catOrder FROM dispatchl2ldb d WHERE d.reasonDesc IN (:topCat1, :topCat2, :topCat3, :topCat4, :topCat5) AND d.line = :line AND d.shiftID = :shiftID AND d.dispatchType IN ('Waiting On', 'Operational Fix', 'FDT') AND d.deleted = 0 GROUP BY d.reasonDesc ORDER BY catOrder ASC ```