Percentage of total

[RichardMGreen]

Hi all

I have a query which produces values for each entry in the table. For Example

Line1     10
Line2     12
Line3     17
Line4     7

What I would like to do is to add a third column which would give me a percantage of total. Therefore the table becomes:-

Line1     10     0.21
Line2     12     0.26
Line3     17     0.37
Line4     7      0.15

I've tried using Expr1/sum(Expr1) but it gives me 100% for each line.

Anyone any ideas?

 

[jackd]

Something like this should do it.

SELECT TLines.LineNo
, TLines.LineAmt
, [LineAmt]/(Select Sum(TLines.LineAmt) AS SumOfLineAmt from tLines) AS Percentage
FROM TLines
GROUP BY TLines.LineNo
, TLines.LineAmt;

 

[RichardMGreen]

Thanks for that.
Unfortunately, I'm trying not to use SQL as I'll be passing the database on to people who don't know it.
They can, however, build normal queries.

 

[jackd]

Well, you could make it an option on a Form and invoke it via a Button.

That was just the SQL for a query built using the wizard.

Here's what the query looks like:
http://www.4shared.com/photo/bbZAezAi/TlinesQuery.html