Pie Chart SQL Query

What I am trying to accomplish is sum each column value and filter based on the date range picker value, also exclude any columns that sum a 0 value (so the pie chart does not show many legend items that have no value). This is the part I cannot figure out how to do, I’ve tried nesting IF statements and changing the WHERE clause but it does not seem to work how I’ve done it. Does anyone have any suggestions ?

What database software are you using? You may be able to add a HAVING clause:

I think that will be question for our IT department if you are looking for specific SQL version below is what I found about the database connection driver.

You’re using Microsoft SQL Server, so you should be able to use the HAVING clause:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql?view=sql-server-2017

2 Likes

I think I read somewhere, that having NULLS in a table can cause issues when trying to do math queries? Is that true ?

Can a line in the query be added to use 0 instead of Null.

This might not be related to your issue, I just noticed a lot of NULLS in your table. Would it be better to use 0 instead ?

1 Like

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

Sample Window.

PieChartFilterZeroValues_2018-05-31_1451.proj (10.2 KB)

2 Likes

Very nice Jordan! Thank you for your time and response, I’m picking up what your putting down it makes sense I’ve tried a form of this. The only thing I’m unsure about is when I do the SQL query it pulls up an error, I thought it had to do with the COALESCENCE parenthesis but even when I close them. I’m already manipulating the colors with scripting based on highest value. I think Im going to apply the method using you’re for loop to filter data, Thanks!


I modified the Query and added the HAVING clause , it did not work it was still returning and populating 0 & null values in the pie chart.

Oops! Sorry about that! COALESCE should take two arguments. It will return the first non-null value

COALESCE (SUM(x),0) AS colName

1 Like