Calculate a field % of total

Topic: Calculate a field % of total (Read 1234 times) previous topic - next topic

Calculate a field % of total

May 29, 2020, 04:54:14 AM

Hi Guys,

I have made a web app using PHPRad, and need to report on the % a field value compared to the sum of all the field values..

e.g.

Table:

ID	Process	Value
1	ArcSpray	10
2	Machine	5
3	ArcSpray	12
4	ArcSpray	20
5	Machine	24
6	ArcSpray	34
7	ArcSpray	100

I want to find the percent (%) that ArcSpray is compared to the total in relation to the values.
the simple math is,
SUM (Values WHERE Process = ArcSpray) / SUM (Values) * 100 = %

I have done this in excel and access (via multiple queries)

RESULT:

Process	Percent of Total
ArcSpray	86.27

Can someone please help how I may be able to get this done?

Re: Calculate a field % of total

Reply #1 – May 29, 2020, 10:46:13 AM

@Matt_NMS‍ your query should look like this.

Code: [Select]

SELECT ((SELECT SUM (Values) FROM table  WHERE Process = 'ArcSpray') / SUM (Values)) * 100 FROM table

Note: this was not tested. the best way to get a good query is to test it while you modify it.

Re: Calculate a field % of total

Reply #2 – June 03, 2020, 02:39:10 AM

THanks, i'll have some more goes at it this week hopefully.