First of all, I have researched everywhere to get this query sorted and I cant find an answer that solved my issue so here it is.
I have this query:
SELECT
[Report Date],
COUNT(CASE WHEN [total_ahr_cap] = '0' OR [standing_load] = '0'THEN NULL ELSE 1 END) AS [Zero Values],
COUNT(CASE WHEN [total_ahr_cap] / [standing_load] > '12' THEN 1 ELSE NULL END) AS [Green Zone],
COUNT(CASE WHEN [total_ahr_cap] / [standing_load] < '12' AND [total_ahr_cap] / [standing_load] >= '10'
THEN 1 ELSE NULL END) AS [Yellow Zone],
COUNT(CASE WHEN [total_ahr_cap] / [standing_load] < '10' THEN 1 ELSE NULL END) AS [Red Zone],
COUNT(CASE WHEN [total_ahr_cap] IS NULL OR [standing_load] IS NULL THEN 1 END)AS [Null Values],
COUNT(CASE WHEN [total_ahr_cap] / [standing_load] > '0' THEN 1 ELSE NULL END) AS [Total]
FROM [dbo].[dc_chargers$]
WHERE [Report Date] = 'March 2016' AND sla_no NOT LIKE ('%South%')
GROUP BY [Report Date]
Bear in mind that I copy and paste this query to get results for several months of the year. The result that I am expecting is the count of how many fields fall under the 'green', 'yellow', 'red' and 'null' value fields as my bosses require this info every month but whenever the [standing_load] value or the [total_ahr_cap] values are 0 - I get a divide by zero error.
I need to have the count of the figures that re divided by 0 as I cannot change them to NULL values so how can I get around this as everytime that I get the divide by zero error the query does not count the month of the figures that have the 0 in and I have to change them to NULL value but that is giving me inaccurate data.
please see below what the query should look like:
Report Date Green Zone Yellow Zone Red Zone Null Values Total
2015-12-01 276 5 5 16 286
2016-01-01 365 5 5 24 375
2016-02-01 376 4 5 25 384
as you can see, it counts all the other months but not the month that has 0 values.
help please!
To avoid Divide by 0 exception in you query, in each case where you do divide operation first check if divider is greater than 0. Also why comparing numerical values to string?
Also your 5th case is missing else part.
See code below:
SELECT
[Report Date],
COUNT(CASE WHEN [total_ahr_cap] = 0 OR [standing_load] = 0 THEN NULL ELSE 1 END) AS [Zero Values],
COUNT(CASE WHEN [standing_load] > 0 and [total_ahr_cap] / [standing_load] > 12 THEN 1 ELSE NULL END) AS [Green Zone],
COUNT(CASE WHEN [standing_load] > 0 and [total_ahr_cap] / [standing_load] < 12 AND [total_ahr_cap] / [standing_load] >= 10
THEN 1 ELSE NULL END) AS [Yellow Zone],
COUNT(CASE WHEN [standing_load] > 0 and [total_ahr_cap] / [standing_load] < 10 THEN 1 ELSE NULL END) AS [Red Zone],
COUNT(CASE WHEN [standing_load] > 0 and [total_ahr_cap] IS NULL OR [standing_load] IS NULL THEN 1 ELSE NULL END)AS [Null Values],
COUNT(CASE WHEN [standing_load] > 0 and [total_ahr_cap] / [standing_load] > 0 THEN 1 ELSE NULL END) AS [Total]
FROM [dbo].[dc_chargers$]
WHERE [Report Date] = 'March 2016' AND sla_no NOT LIKE ('%South%')
GROUP BY [Report Date]
Also a small advice because I'm not sure if it is acceptable in your solution but I'd change count to sum and nulls to 0.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments