Percentage SQL function

[matpj]

how do I write SQL to get a percentage of two other columns??
e.g. (COLUMNA/COLUMNB)*100
I have tried just writing the expression in the Query builder but this doesn't work!!

 

[TonyJollans]

Hi matpj,

What does it do? How, exactly, have you input it?

Entering ..

=columna/columnb*100

.. should give you ..

Expr1: [columna]/[columnb]*100

.. which works fine for me (with numeric values of course).

Two possibilities spring to mind:

1. If your column names are not actually columna and columnb but either have spaces (or other special characters} in or are 'reserved' words you might have to explicitly enter the brackets round them.

2. If you have more than one columna or columnb in your query you will need to qualify the column names with table names.

Enjoy,
Tony

 

[matpj]

is there not a percent function within SQL?

the column A and column B things were just an example.
I have a table formed from 4 seperate tables:

Products V8 V9 V10 V11
ProductA 37 47 50 5
ProductB 440 686 333 70
ProductC 228 446 167 13
ProductD 165 260 63 15

I need to have the columns with data to be a percentage of the total of each columns.

The product column is actually coming from table V10(although the values are common for each other table) and has a 'group by' so that there are no duplicate values.
there is a join by Product between each of the tables.
Each table has two columns: PRODUCT and COUNT
does this make it clearer.
it is not letting me write Percent8: V8.[COUNT]/SUM(V8.[COUNT])*100

as I gt an error message about trying to execute a query that does not include the specified expression as part of an aggregate function!

 

[lespaul]

That error occurs when you are using an aggregate function (like count, sum, etc) and you haven't included all your select fields in the group by function.

So if you had the following SQL:

SELECT FIELD1, FIELD2, COUNT(FIELD3) FROM TABLE

you would get the aggregate function error, if you change it to:

SELECT FIELD1, FIELD2, COUNT(FIELD3) FROM TABLE GROUP BY FIELD1, FIELD2

then you won't get the error.

HTH


Leslie

 

[dhookom]

You could do this in a report where it would be quite easy. If you must do it in a query, you could use a subquery.
Percent8: V8.[COUNT]/(SELECT SUM([COUNT]) FROM V8 v81 WHERE v81.Product = V8.Product)*100
This assumes your table name is V8.

Duane
MS Access MVP

 

[matpj]

dhookom,
I would like to do it in a query,
in your example, what is the v81? and should this come after V8 without any operators??

 

[dhookom]

v81 is an alias for V8 which I think from reading another of your posts/threads is V8Master. The subquery needs to create another instance of V8Master so it must create an alias for it.

Duane
MS Access MVP