# Getting the sum of a value per hour and then getting percentages based on a high and low condition

**URL:** <https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551>\
**Category:** Ignition\
**Created:** [March 16, 2021, 9:07pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551 "2021-03-16T21:07:27Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 16, 2021, 9:07pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/1 "2021-03-16T21:07:27Z")

</div>

I have a query that I want to modify. The current query sums the usage per hour. I want to modify it to be able to just get the percentages of the value greater than 610 and a percentage below 610. Also need to make the two percentages equal 100.

```auto
SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
FROM ChlUsage
WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
GROUP BY CONVERT(VARCHAR(13), DateTm, 121)
ORDER BY DateHour desc

```

current data results

```auto
  DateHour / TotalUsage
----------------------------
2021-03-16 15 / 197.701171875
2021-03-16 14 / 490.859375
2021-03-16 13 / 578.30078125
2021-03-16 12 / 523.654296875
2021-03-16 11 / 417.15625
2021-03-16 10 / 245.65625
2021-03-16 09 / 406.498046875
2021-03-16 08 / 403.06640625

```

Desired results

```auto
InPermitRangePercentage / OverPermitRangePercentage
           74.70 / 25.30

```

---

<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:** [March 17, 2021, 3:30pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/2 "2021-03-17T15:30:35Z")

</div>

Sorry, I had meant to answer sooner, but I must have seen something shiny to distract me…

After all this time, I guess I don’t know what DB you’re using (sorry), but you can use your existing query as a subquery. Using the ‘1 minus’ for calculating the other percentage helps to guarantee that they add to 100 percent by avoiding any rounding errors.

```sql
SELECT SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       (1-SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)
       ORDER BY DateHour desc) h
```

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:25pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/3 "2021-03-19T16:25:55Z")

</div>

I am using SQL Server Lite, soon to I will be upgrading to the full version.

When I ran your query I get this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

---

<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:** [March 19, 2021, 4:29pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/4 "2021-03-19T16:29:35Z")

</div>

Oh! Now that I think about it, try it without the ORDER BY. You wouldn’t really need it in a subquery, anyway.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:31pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/5 "2021-03-19T16:31:33Z")

</div>

after removing it

I get this error:  
Incorrect syntax near ‘)’.

---

<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:** [March 19, 2021, 4:32pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/6 "2021-03-19T16:32:24Z")

</div>

Did you take out the whole clause?

```sql
SELECT SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       (1-SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h

```

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:33pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/7 "2021-03-19T16:33:42Z")

</div>

just the order by, take out the group by as well?

---

<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:** [March 19, 2021, 4:34pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/8 "2021-03-19T16:34:23Z")

</div>

The group by should still work.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:35pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/9 "2021-03-19T16:35:21Z")

</div>

> [@JordanCClark](#):
>
> SELECT SUM(CASE WHEN h.TotalUseage \<= 610 THEN 1.0 ELSE 0.0 END)/COUNT(_) \* 100 as InPermitRangePercentage, (1-SUM(CASE WHEN h.TotalUseage \<= 610 THEN 1.0 ELSE 0.0 END)/COUNT(_)) \* 100 as OverPermitRangePercentage FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage FROM ChlUsage WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1) GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h

error was just the name 'TotalUseage' Had to change it to 'TotalUsage'

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:38pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/10 "2021-03-19T16:38:42Z")

</div>

that worked!

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:47pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/11 "2021-03-19T16:47:26Z")

</div>

Is the Greater than or equal to for each one supposed to be the same?

```auto
SELECT SUM(CASE WHEN h.TotalUsage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       (1-SUM(CASE WHEN h.TotalUsage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h

```

---

<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:** [March 19, 2021, 4:50pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/12 "2021-03-19T16:50:55Z")

</div>

Yep. That way the calculation is the same, the second one is 1 minus the calculation to get the other side.

You could certainly try it this way, my only caveat would entail any rounding errors so that the two don’t get to 100%

```sql
SELECT SUM(CASE WHEN h.TotalUsage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       SUM(CASE WHEN h.TotalUsage > 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h

```

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [March 19, 2021, 4:56pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/13 "2021-03-19T16:56:07Z")

</div>

ok, I tried it but the results is the same. I will stick with doing it the way you posted the solution. Just wanted to make sure the data I would be displaying was correct. Thanks alot for the help!

---

<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:** [March 19, 2021, 4:59pm UTC](https://forum.inductiveautomation.com/t/getting-the-sum-of-a-value-per-hour-and-then-getting-percentages-based-on-a-high-and-low-condition/44551/14 "2021-03-19T16:59:23Z")

</div>

Since they’re double precsion, any rounding errors would likely be negligible. The subtraction method is one I use starting WAAAY back in 16-bit days. lol