# Divide by zero error encountered, SQL query in Power Table

**URL:** <https://forum.inductiveautomation.com/t/divide-by-zero-error-encountered-sql-query-in-power-table/87307>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [March 13, 2024, 10:04pm UTC](https://forum.inductiveautomation.com/t/divide-by-zero-error-encountered-sql-query-in-power-table/87307 "2024-03-13T22:04:43Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![rigoberto.reyes](https://avatars.discourse-cdn.com/v4/letter/r/65b543/32.png) [@rigoberto.reyes](https://forum.inductiveautomation.com/u/rigoberto.reyes)\
**Post date:** [March 13, 2024, 10:04pm UTC](https://forum.inductiveautomation.com/t/divide-by-zero-error-encountered-sql-query-in-power-table/87307/1 "2024-03-13T22:04:43Z")

</div>

Hi.

I have the following query into a Power Table object, which returns divide by zero error encountered message. how can it be solved?

```auto
Select MAchineID, MIN([CurrTotalGasConsumption]) as 'Inicial Consumption', MAX([CurrTotalGasConsumption]) AS 'Final Consumption',
MAX([CurrTotalGasConsumption]) - MIN([CurrTotalGasConsumption]) AS 'Gas Consumption (m3)',
MAX(TotalChargedWeight) - MIN(TotalChargedWeight) as ChargedWeight,
((MAX(TotalChargedWeight) - MIN(TotalChargedWeight))*1000) /(MAX([CurrTotalGasConsumption]) - MIN([CurrTotalGasConsumption])) as 'm3/ton'

FROM [IgnitionRuntime].[dbo].[W01_MELTING_GAS_CONSUMPTION]
WHERE 
		Timestamp >= '2024-03-13 07:00:00'
	AND
		Timestamp <= '2024-03-13 15:00:00'
group by MachineID
order by MAchineID asc

```

 ![imagen](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/1/11b56e6422c966a0a7a9e1a649a9a7b1b3f0d85c.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [March 13, 2024, 11:22pm UTC](https://forum.inductiveautomation.com/t/divide-by-zero-error-encountered-sql-query-in-power-table/87307/3 "2024-03-13T23:22:45Z")

</div>

Tidied up query:

```auto
SELECT 
    MAchineID, 
    MIN([CurrTotalGasConsumption]) AS 'Inicial Consumption', 
    MAX([CurrTotalGasConsumption]) AS 'Final Consumption',
    MAX([CurrTotalGasConsumption]) 
    - MIN([CurrTotalGasConsumption]) AS 'Gas Consumption (m3)',
    MAX(TotalChargedWeight) - MIN(TotalChargedWeight) as ChargedWeight,
    (
      (MAX(TotalChargedWeight) - MIN(TotalChargedWeight)) * 1000) 
      / (MAX([CurrTotalGasConsumption]) - MIN([CurrTotalGasConsumption])
    ) AS 'm3/ton'
FROM [IgnitionRuntime].[dbo].[W01_MELTING_GAS_CONSUMPTION]
WHERE
    Timestamp >= '2024-03-13 07:00:00'
AND
    Timestamp <= '2024-03-13 15:00:00'
GROUP BY MachineID
ORDER BY MAchineID asc

```

The only place you can have a divide by zero is in the division line,

```auto
      / (MAX([CurrTotalGasConsumption]) - MIN([CurrTotalGasConsumption])

```

You forgot to say which database engine you are using.  
For MS SQL you can do something like this:

```auto
SELECT CASE WHEN 
    (MAX([CurrTotalGasConsumption]) - MIN([CurrTotalGasConsumption]) = 0 
    THEN NULL AS 'm3/ton'
    ELSE 
    (
      (MAX(TotalChargedWeight) - MIN(TotalChargedWeight)) * 1000) 
      / (MAX([CurrTotalGasConsumption]) - MIN([CurrTotalGasConsumption])
    ) AS 'm3/ton'

END

```

There will be something similar for other database engines.