# Sum of a total as a percentage Sql query

**URL:** <https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594>\
**Category:** Ignition\
**Created:** [November 1, 2019, 2:51pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594 "2019-11-01T14:51:28Z")\
**Posts on this page:** 13\
**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:** [November 1, 2019, 2:51pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/1 "2019-11-01T14:51:28Z")

</div>

This is what I have below. This will get the hours total but how would I go about getting the percentage of the total in another column?

```auto
Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours
From WorkOrder (nolock)
Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
Group by Category

```

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [November 1, 2019, 3:15pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/2 "2019-11-01T15:15:25Z")

</div>

Not quite sure what you’re trying to get the percentage of, but I’ll assume you want something like the percentage of MaintTech1Hours of the total hours.

SQL can do the math for you. In MySQL your select would become something like  
`SELECT Category, SUM (MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) AS 'Hours', concat(format(100 * MaintTech1Hours / 'Hours', 0), '%') AS 'Maint Tech 1 %'`

This would give you the percent with zero decimal places and a % sign after the number. Once you’ve aliased that sum to Hours, you can refer to it again in other places in your query.

[edit] Whelp, MySQL allows this kind of reference but gives 0 as a result. :shakesfist:

---

<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:** [November 1, 2019, 5:18pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/3 "2019-11-01T17:18:09Z")

</div>

![Hours](https://us1.discourse-cdn.com/inductiveautomation/original/2X/0/0ff43a66f09431ec04f9805e6d610288dbca9522.png)

This is the result I am looking for but I need to figure out how to replace 60 which is the total right now for the 3 values.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [November 1, 2019, 6:45pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/4 "2019-11-01T18:45:02Z")

</div>

Try this

```auto
Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours, 
(SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours)/x.Total)*100 Percentage
From WorkOrder (nolock)
CROSS
JOIN ( Select SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) From WorkOrder (nolock) Total) x 
Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
Group by Category

```

**EDIT** You may have to put the where clause inside the join too

---

<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:** [November 1, 2019, 8:10pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/5 "2019-11-01T20:10:49Z")

</div>

![Hours%20error](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6a9e4cc5efb7740db1dd1661a44f2cbf0d62a051.png)

This is the error I get when I run the query

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [November 1, 2019, 8:30pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/6 "2019-11-01T20:30:42Z")

</div>

It’s hard to tell since I can’t test the query, but it may be the where clause at the end that is causing this. Try

```auto
Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours, 
(SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours)/x.Total)*100 Percentage
From WorkOrder (nolock)
Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
CROSS
JOIN ( Select SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) From WorkOrder (nolock) Total
Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)) x 
Group by Category

```

Here is the simple query I ran on my system to figure out the basics of this logic

```auto
select user, sum(salh),(sum(salh)/ x.total)*100 Percentage
from sew_history
cross
JOIN (select sum(salh) total from sew_history) x
group by user

```

---

<div class="post-metadata">

**Author:** ![smithclarkson1](https://avatars.discourse-cdn.com/v4/letter/s/839c29/32.png) [@smithclarkson1](https://forum.inductiveautomation.com/u/smithclarkson1)\
**Post date:** [November 1, 2019, 8:35pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/7 "2019-11-01T20:35:01Z")

</div>

Thanks for the solution Ma’am…it is helpful…! 🙂 [redtube.vin](https://redtube.vin)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [November 1, 2019, 8:40pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/8 "2019-11-01T20:40:30Z")

</div>

Unless something is different in MySQL 8.0, you cannot refer to an alias in this way, you have to assign it to a variable which isn’t allowed in the JDBC driver. Assigning a value in a query would have to be done database side as a stored procedure.

---

<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:** [November 1, 2019, 9:03pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/9 "2019-11-01T21:03:14Z")

</div>

it doesn’t like the cross join either

---

<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:** [November 1, 2019, 9:37pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/10 "2019-11-01T21:37:53Z")

</div>

is there another way around it?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [November 2, 2019, 4:53am UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/11 "2019-11-02T04:53:16Z")

</div>

This is what [Window Functions](https://www.postgresql.org/docs/10/functions-window.html) are for. Try this:

```auto
Select Category,
    Hours, 
    100.0*Hours/(SUM(Hours) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)) As Pct
FROM (
    Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours
    From WorkOrder (nolock)
    Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by Category
) subq

```

---

<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:** [November 2, 2019, 11:32am UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/12 "2019-11-02T11:32:38Z")

</div>

it gives me this error when running this query  
 ![query%20error](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/d254d2f6bbc84f5c75026dbce5c3e401cc326f5f.png)

---

<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:** [November 2, 2019, 1:45pm UTC](https://forum.inductiveautomation.com/t/sum-of-a-total-as-a-percentage-sql-query/29594/13 "2019-11-02T13:45:01Z")

</div>

I had to add the “Order by Category” before ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Thanks a lot for the help!

```auto
Select Category,
    Hours, 
    100.0*Hours/(SUM(Hours) OVER (Order by Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours
    From WorkOrder (nolock)
    Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by Category
) subq

```