# Thread: Eliminate Division by Zero

1. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941

## Unanswered: Eliminate Division by Zero

This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.

Any suggestions on how to improve this?

Code:
```SELECT     FL.REPORT_PLANT,
[JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END),
[FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END),
[MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END),
[APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END),
[MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END),
[JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END),
[JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END),
[AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END),
[SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END),
[OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END),
[NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END),
[DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END)
FROM         PRODUCTION_CMPLT PC INNER JOIN
FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND
PC.PROD_LINE = FL.PROD_LINE  INNER JOIN
PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT
WHERE     YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB'
GROUP BY FL.REPORT_PLANT```

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
```SELECT [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/
SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END)```
the problem lies in your second CASE expression yielding 1 when the sum is not =1. What you want is for the whole expression to yield 0 if that =1...
I think this might be the what you want
Code:
```SELECT
[JAN]=  SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN (PC.TONS * 2000 / PM.EA_WT) / (PC.HOURS * 60) ELSE 0 END)```

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Or...
Code:
`...ELSE 1`

4. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
(select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm

Or

Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
And Pm. Ea_wt <> 0

????

5. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
re: georgev

ahhh... But I need Sum(Units)/Sum(Minutes), which is not the same as
Sum(Units/Minutes)

Changing to Else 1 yields a "false positive" in the off months, and throws off my numbers.

6. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Originally Posted by Brett Kaiser
(select Case When Ea_wt = 0 Then 1 Else Ea_wt End From Prod_master) As Pm

Or

Where Year(pc.mnthyr) = Year(getdate()) And Pm.uom<>'lb'
And Pm. Ea_wt <> 0

????
I don't have a problem with ea_wt. There is a constraint on the table that doesn't allow that field to be equal to 0.

7. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
I think you want to do this

Code:
```    SELECT  FL.REPORT_PLANT,
, [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM  PRODUCTION_CMPLT PC
INNER JOIN  FACILITY_LINES FL
ON  PC.MANUF_SITE = FL.MANUF_SITE
AND  PC.PROD_LINE = FL.PROD_LINE
INNER JOIN  PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND  MONTH(PC.MNTHYR) = 1
AND  PM.UOM<>'LB'
GROUP BY  FL.REPORT_PLANT
UNION ALL
SELECT  FL.REPORT_PLANT,
, [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM  PRODUCTION_CMPLT PC
INNER JOIN  FACILITY_LINES FL
ON  PC.MANUF_SITE = FL.MANUF_SITE
AND  PC.PROD_LINE = FL.PROD_LINE
INNER JOIN  PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND  MONTH(PC.MNTHYR) = 2
AND  PM.UOM<>'LB'
GROUP BY  FL.REPORT_PLANT
UNION ALL
..ect```

8. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
That looks promising. I'll give it a go.

9. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Actually, with that one you will need a labl;e for each union to know what month it is...but you could do this as columns like you have

Code:
```    SELECT *
FROM FACILITY_LINES xxx
INNER JOIN (

SELECT  FL.REPORT_PLANT
, [JAN] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM  PRODUCTION_CMPLT PC
INNER JOIN  FACILITY_LINES FL
ON  PC.MANUF_SITE = FL.MANUF_SITE
AND  PC.PROD_LINE = FL.PROD_LINE
INNER JOIN  PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND  MONTH(PC.MNTHYR) = 1
AND  PM.UOM<>'LB'
GROUP BY  FL.REPORT_PLANT) AS JAN
ON xxx.REPORT_PLANT = JAN.REPORT_PLANT
INNER JOIN (
SELECT  FL.REPORT_PLANT
, [FEB] = PC.TONS * 2000 /PM.EA_WT/PC.HOURS*60
FROM  PRODUCTION_CMPLT PC
INNER JOIN  FACILITY_LINES FL
ON  PC.MANUF_SITE = FL.MANUF_SITE
AND  PC.PROD_LINE = FL.PROD_LINE
INNER JOIN  PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND  MONTH(PC.MNTHYR) = 2
AND  PM.UOM<>'LB'
GROUP BY  FL.REPORT_PLANT) AS FEB
ON xxx.REPORT_PLANT = FEB.REPORT_PLANT
..ect```

10. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
For some reason -that I can't figure out- I had to use SELECT DISTINCT.
I also had to use a LEFT JOIN for each subquery...

Code:
```SELECT DISTINCT FL.REPORT_PLANT, JAN, FEB, MAR, APR, MAY
FROM FACILITY_LINES FL
LEFT OUTER JOIN (
SELECT  FL.REPORT_PLANT, [JAN] =sum( PC.TONS * 2000 /PM.EA_WT)/sum(PC.HOURS*60)
FROM  PRODUCTION_CMPLT PC
INNER JOIN  FACILITY_LINES FL
ON  PC.MANUF_SITE = FL.MANUF_SITE
AND  PC.PROD_LINE = FL.PROD_LINE
INNER JOIN  PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND  MONTH(PC.MNTHYR) = 1
AND  PM.UOM<>'LB'
GROUP BY  FL.REPORT_PLANT) AS JAN
ON FL.REPORT_PLANT = JAN.REPORT_PLANT
LEFT OUTER JOIN (
SELECT  FL.REPORT_PLANT
, [FEB] = SUM(PC.TONS * 2000 /PM.EA_WT)/sUM(PC.HOURS*60)
FROM  PRODUCTION_CMPLT PC
INNER JOIN  FACILITY_LINES FL
ON  PC.MANUF_SITE = FL.MANUF_SITE
AND  PC.PROD_LINE = FL.PROD_LINE
INNER JOIN  PROD_MASTER PM
ON PC.PRODUCT=PM.PRODUCT
WHERE  YEAR(PC.MNTHYR) = YEAR(GETDATE())
AND  MONTH(PC.MNTHYR) = 2
AND  PM.UOM<>'LB'
GROUP BY  FL.REPORT_PLANT) AS FEB
ON FL.REPORT_PLANT = FEB.REPORT_PLANT
...```
That's exactly what I needed.

Thanks
Mark

11. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Why...what did the output look like without distinct?

12. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
The Facility Lines table can have multiple production lines for each report plant.

Facility Lines
==========
PLANT
PROD_LINE (pk)
REPORT_PLANT

I was getting one record for each production line

13. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
I still don't get it.

I would definetly figure out why, so as to not use DISTINCT as a hack

14. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Since the table has data like this:

Plant----Prod_Line----Report_Plant
==========================
-1---------A---------PLANT1
-1---------B---------PLANT1
-1---------C---------PLANT1
-2---------D---------PLANT2
-3---------E---------PLANT3

My output looks like (without using distinct)

Report_Plant--JAN--FEB--MAR...
========================
PLANT1-------10---9-----10
PLANT1-------10---9-----10
PLANT1-------10---9-----10
PLANT2-------17---24----25
PLANT3-------8----6-----7

15. Registered User
Join Date
Jul 2007
Posts
61
So shouldn't you be grouping?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•