Pie Chart SQL Query

Ignition
6

I don’t think HAVING will work, as it’s all in one row. This would have been an instance where a tall format would have worked better.

I would use a dataset custom property to hold the query, only coalescing nulls to zeroes (Craig, to answer your question, SUM will ignore nulls, but if all the values are null, so is the result :slight_smile: ):

EDIT: Fixed COALESCE per conversation below.

SELECT
  COALESCE(SUM(Crush_BF1),0) AS CrushBF1,
  COALESCE(SUM(Crush_BF2),0) AS CrushBF1,
  COALESCE(SUM(Crush_BF3),0) AS CrushBF1,
  COALESCE(SUM(TTC2_BF1),0) AS TTC2BF1,
  COALESCE(SUM(TTC3_BF1),0) AS TTC3BF1,
  COALESCE(SUM(TTC3_BF2),0) AS TTC3BF2,
  COALESCE(SUM(TTC3_BF3),0) AS TTC3BF3

FROM Backflow_Preventers

WHERE
  t_stamp >= '{Root Container.TimeStart}' AND
  t_stamp <= '{Root Container.TimeStop}'

Then use a propertyChange script to filter the results (my example uses ‘dataIn’ as the custom property):

if event.propertyName == 'dataIn':

  datasetIn = event.source.dataIn
  
  dataIn = system.dataset.toPyDataSet(datasetIn)
  
  headersIn = system.dataset.getColumnHeaders(datasetIn)
  headersOut =[]
  dataOut = []
  
  for row in dataIn:
    rowOut = []
    for header in headersIn:
      print header, row[header]
      if row[header] != 0:
        headersOut.append(header)
        rowOut.append(row[header])
    dataOut.append(rowOut)
  print headersOut, dataOut
  event.source.data = system.dataset.toDataSet(headersOut, dataOut)
1 Like