calculating a rate

[Lifeseeker]

Hi there,

I have a simple query that calculates a rate.

Formula is just x/y, where x represents the number of interested records, and y is the total number of record.

Can this be done in the expression or in the query grid? I wonder how should the syntax go.

Thanks for any comment.

 

[jdraw]

Yes it can be done.
The following is a general syntax

Select Count(fld1) AS x
, (select count(*) from MyTable ) as y
, x/y as Rate
from MyTable
Where
fld1 = "IsInterested"

 

[Lifeseeker]

Yes it can be done.
The following is a general syntax

Thank you and it works perfectly.

 

[Lifeseeker]

Yes it can be done.
The following is a general syntax

Hi,

If I also wanted to calculate this rate based on another condition, can it be done in SQL as well?

Select Count(fld1) AS x
, (select count(*) from MyTable ) as y
, x/y as Rate
from MyTable
Where
fld1 = "IsInterested"

So the location constraint is on both the numerator and the denominator. In other words, I want to find out what the x/y rate is for location A, location B, and location C, individually.

It seems that a WHERE clause needs to be used, but I had a problem when I put the location condition in the numerator. The query won't run.

Any thought/comment much appreciated.