Sum of a total as a percentage Sql query

Ignition
1

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?

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
2

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:

3

Hours
Hours903Ă—313 13.6 KB

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.

4

Try this

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

5

Hours%20error

This is the error I get when I run the query

6

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

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

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
7

Thanks for the solution Ma’am…it is helpful…! :slight_smile: redtube.vin

1 Like
8

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.

9

it doesn’t like the cross join either

10

is there another way around it?

11

This is what Window Functions are for. Try this:

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
1 Like
12

it gives me this error when running this query
query%20error

13

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

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
1 Like