Divide by zero error encountered, SQL query in Power Table

Ignition
1

Hi.

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

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
imagen1234×265 68.8 KB

3

Tidied up query:

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,

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

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

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.

2 Likes