CODEWITHSUNDEEP
sqlsql-servert-sql
Raul Gonzales
Raul Gonzales

Reputation: 906

Count dividing by 0

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!

Upvotes: 2

Views: 130

Answers (2)

gzaxx
gzaxx

Reputation: 17600

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.

Upvotes: 2

Zahiro Mor
Zahiro Mor

Reputation: 1718

try this

SELECT
    [Report Date],
    sum(CASE WHEN [total_ahr_cap] = 0 OR [standing_load] = 0 THEN 1 ELSE 0 END) AS [Zero Values],
    sum(CASE WHEN [total_ahr_cap] / [standing_load] > 12 and [standing_load] is not null THEN 1 ELSE 0 END) AS [Green Zone],
    sum(CASE WHEN [total_ahr_cap] / [standing_load] < 12 AND [total_ahr_cap] / [standing_load] >= 10 and [standing_load] is not null
          THEN 1 ELSE 0 END) AS [Yellow Zone],
    sum(CASE WHEN [total_ahr_cap] / [standing_load] < 10 and [standing_load] is not null THEN 1 ELSE 0 END) AS [Red Zone],
    sum(CASE WHEN [total_ahr_cap] IS NULL OR [standing_load]  IS NULL THEN 1 ELSE 0 END) AS [Null Values],
    sum(CASE WHEN [total_ahr_cap] / [standing_load] > 0 and [standing_load] is not null THEN 1 ELSE 0 END) AS [Total]

FROM [dbo].[dc_chargers$]
WHERE [Report Date] = 'March 2016' AND sla_no NOT LIKE ('%South%')
GROUP BY [Report Date]

Upvotes: 0

Related Questions