Order of Precedent in SQL

[Kim Baker]

Hi guys
Hope someone can help.

I have a field in a table that has '0' as a default value that
indicates that no result has been temporarily stored.
When updated many of these values will change to the range of 95.00 to
103.00. Others will stay at '0'.

I am attempting to create a Sql statement that will return the average
of the non zero values.

In the QBE grid I can see all these values. When I add the criteria
'>0', I see only the non zero values.
However, when I select the aggregate function and choose Avg, it
returns a value of 84 instead of something between 95.00 and 103.00.
It seems to me that this might indicate that the SQL is averaging all
values and ignoring the criteria field. Not what I am after.

Pseudo code is similar to this:
SELECT Avg(tblTable1.field1)
FROM
WHERE this AND that AND ((Avg(tblTable1.field1)>0)

Can anyone point me to an example code that would allow me to first
select the non zero values and then average only those values?
Cheers

Remove undies to reply

 

[Guest]

hi,

try this

SELECT avg(TableB.val)
FROM TableB
where
TableB.val>0

pls let me know if this helps u

Thanks

With regds

Sunil.T

 

[Tom Ellison]

Dear Kim:

Just modifying the sample code you gave:

SELECT Avg(tblTable1.field1)
FROM tblTable1
WHERE this AND that AND tblTable1.field1 > 0

Your filter is not to omit those whose average is not > 0, as what you wrote
seems to state, but to omit those rows that are 0 from the average.

Tom Ellison

 

[Kim Baker]

Many thanks Sunil and Tom for your replies. Yes, my logic was at fault
and the problem is solved.
Cheers
Kim

Hi guys
Hope someone can help.

I have a field in a table that has '0' as a default value that
indicates that no result has been temporarily stored.
When updated many of these values will change to the range of 95.00 to
103.00. Others will stay at '0'.

I am attempting to create a Sql statement that will return the average
of the non zero values.

In the QBE grid I can see all these values. When I add the criteria
'>0', I see only the non zero values.
However, when I select the aggregate function and choose Avg, it
returns a value of 84 instead of something between 95.00 and 103.00.
It seems to me that this might indicate that the SQL is averaging all
values and ignoring the criteria field. Not what I am after.

Pseudo code is similar to this:
SELECT Avg(tblTable1.field1)
FROM
WHERE this AND that AND ((Avg(tblTable1.field1)>0)

Can anyone point me to an example code that would allow me to first
select the non zero values and then average only those values?
Cheers

Remove undies to reply

Remove undies to reply