calculate percentages

[phpatrick]

COLUMN
52,24
255,11
0,3

I need to get the percentage of a column.
I think I should first find out the total. Then divided and multiplied with 100.

1. How to get the total of the row. Is it possible to put it directly in my expression ?

2. Is the formula correct ? number/total * 100

 

[lespaul]

yes to get a percentage you take the number divided by the total times 100....

SELECT Column/Sum(Column) * 100 As ColumnPercentage From Tablename

should get you what you want

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[jebry]

Hi!

In you query use:

Select Column1/(Column1+Column2)*100 As PercentColumn1 From YourTable

Will return the appropriate percentage.

Alternatively, in design view in the Field row put:

Percent Column1 to Total: Column1/(Column1 + Column2)

Then highlight the field and right click on it then choose Properties from the pop-up menu. Click into the Format box in the Properties menu and click on the down arrow. Choose Percent and then your answer will come out as a percentage.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[phpatrick]

SELECT ColumnTotal/Sum(ColumnTotal) * 100 As ColumnPercentage From Table

What is wrong, he refuse to take this, he said, not part of statistical function ?

 

[phpatrick]

Jeff, I do not see the need of Column2.

 

[PHV]

SELECT 100 * ColumnTotal/DSum("ColumnTotal","Table") AS ColumnPercentage
FROM Table

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[phpatrick]

[TOTAL]/DSum("TOTAL","TABLE_TOTAL") AS [PERCENT]

set the propertu of the field to percentage

merci, this works in a new query.

when I try to include this in the same query (where he calculate the totals, this is not working. I suppose you can't make some calculations on another calculated field).

 

[jebry]

Hi phpatrick!

You asked to get the total of a row. To do that you need to add together all of the columns. Did you mean that you wanted the total of the column?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

this is not working
Why not posting the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[phpatrick]

You have reason. I copied the working part in my previous but little problem remains, he refused to take the percentage property.

 

[lespaul]

can you post the entire SQL you are trying to run that gives you an error? The error message might be helpful to us in figuring out why it's not working for you.

the more information you provide the easier it is for us to help you.

 

[phpatrick]

It works fine. Perhaps some syntax trouble. Now there is no need anymore to post my SQL, I do this for my future requests.

txs