# Pie Chart SQL Query

**URL:** <https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231>\
**Category:** Ignition\
**Tags:** database\
**Created:** [May 30, 2018, 9:11pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231 "2018-05-30T21:11:00Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![ndutra](https://avatars.discourse-cdn.com/v4/letter/n/f0a364/32.png) [@ndutra](https://forum.inductiveautomation.com/u/ndutra)\
**Post date:** [May 30, 2018, 9:11pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/1 "2018-05-30T21:11:00Z")

</div>

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 ?

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/fd734111a36f7c7d3529fe1cdc6354a6342763c5.png)

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d37987afd6adae68a80ec5623b31911ce2f84b8a.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 30, 2018, 9:21pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/2 "2018-05-30T21:21:02Z")

</div>

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

> <https://stackoverflow.com/questions/34457514/checking-sum-and-ignoring-zero-values>

---

<div class="post-metadata">

**Author:** ![ndutra](https://avatars.discourse-cdn.com/v4/letter/n/f0a364/32.png) [@ndutra](https://forum.inductiveautomation.com/u/ndutra)\
**Post date:** [May 30, 2018, 9:39pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/3 "2018-05-30T21:39:48Z")

</div>

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.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/42ea673b853ae3a8c6b416d376c8c4e46a70dd64.png)

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [May 30, 2018, 10:48pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/4 "2018-05-30T22:48:19Z")

</div>

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](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql?view=sql-server-2017)

---

<div class="post-metadata">

**Author:** ![craigb](https://avatars.discourse-cdn.com/v4/letter/c/9f8e36/32.png) [@craigb](https://forum.inductiveautomation.com/u/craigb)\
**Post date:** [May 31, 2018, 3:39am UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/5 "2018-05-31T03:39:07Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [May 31, 2018, 6:47pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/6 "2018-05-31T18:47:28Z")

</div>

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 🙂 ):

EDIT: Fixed COALESCE per conversation below.

```auto
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):

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [May 31, 2018, 6:53pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/7 "2018-05-31T18:53:09Z")

</div>

Sample Window.

[PieChartFilterZeroValues\_2018-05-31\_1451.proj](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1cd2acfed17a29120a0265e39736c266382dc16a.proj) (10.2 KB)

---

<div class="post-metadata">

**Author:** ![ndutra](https://avatars.discourse-cdn.com/v4/letter/n/f0a364/32.png) [@ndutra](https://forum.inductiveautomation.com/u/ndutra)\
**Post date:** [May 31, 2018, 9:04pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/8 "2018-05-31T21:04:03Z")

</div>

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!

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/f9c4f2a0dd155d5ebcd91125201665eafbe775c7.png)  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/77ced7e64d875c319ffe94cbd8f98db890f34105.png)

---

<div class="post-metadata">

**Author:** ![ndutra](https://avatars.discourse-cdn.com/v4/letter/n/f0a364/32.png) [@ndutra](https://forum.inductiveautomation.com/u/ndutra)\
**Post date:** [May 31, 2018, 9:12pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/9 "2018-05-31T21:12:12Z")

</div>

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.

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/b/b1ea04b665285fd3b416e853e598de6eef791068.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [May 31, 2018, 9:20pm UTC](https://forum.inductiveautomation.com/t/pie-chart-sql-query/18231/10 "2018-05-31T21:20:45Z")

</div>

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

COALESCE (SUM(x),0) AS colName