SQL Percentages

[Peter Nunez]

How do I calculate percentages using SQL? I have a query
that gens 3 fields. Line Number, UserID and OSType. I
want %'s for the OSType (There are three ostypes). I will
use another query based on the above first query. Unless
it can be done in one query.

Thanks, Peter

 

[Dale Fye]

Peter,

I'm not exactly sure what you want to group on, but try playing with
this concept.

SELECT T.OSType, T.OSCount, (T.OSCount/A.Totals) as OSPct
FROM (SELECT OSType
, Count(OSType) as OSCount
FROM yourTable
GROUP BY OSCount) as T,
(SELECT COUNT(*) as Totals
FROM YourTable) as A

The first subquery counts the number of records by OSType and is
joined to the second subquery (which counts the total number of
records) via a Cartesian join (no linking lines). The outer portion
of the query divides the sum of the counts by OSType by the total
number of records.

--
HTH

Dale Fye


How do I calculate percentages using SQL? I have a query
that gens 3 fields. Line Number, UserID and OSType. I
want %'s for the OSType (There are three ostypes). I will
use another query based on the above first query. Unless
it can be done in one query.

Thanks, Peter

 

[Michel Walsh]

Hi,


Make a computed expression, like:

MyValue / DSum( "MyValue", "tableNameHere", "OSType=""" &
OSType & """" )


assuming OSType is a string, if it is a number:

MyValue / DSum( "MyValue", "tableNameHere", "OSType=" &
SType )



Hoping it may help,
Vanderghast, Access MVP

 

[Peter Nunez]

Thank you very much.

-----Original Message-----
Hi,


Make a computed expression, like:

MyValue / DSum

( "MyValue", "tableNameHere", "OSType=""" &

OSType & """" )


assuming OSType is a string, if it is a number:

MyValue / DSum

( "MyValue", "tableNameHere", "OSType=" &

 

[Peter Nunez]

Thank you very much.