Condition WHERE only for one field

[stone28]

HI all,

It is possible to do something like this:

SELECT tbl1.fld1, (AVG(tbl2.fld1) WHERE tbl2.fld1>0) FROM ......

What I mean here is that I want to calculate avg only from fields >0 but I want to list all records but some will not have AVG at all. It's list of movies and in tbl2 I keep peoples scores (marks for movies) and only some of them have entries. I need >0 becouse the same table keeps people comments and if there is comment left but no score it will be automatically filled with 0.

 

[namliam]

No or not this way, I think you need to make a 'normal' query first
Select tbl1.fld1, AVG(tbl2.fld1) FROM ...... WHERE tbl2.fld1>0

Then join this back to where you need your average, good luck !

 

[MarkK]

You can write a subquery here ...

SELECT Field1, ( SELECT AVG(Field1) FROM Table2 WHERE Field1 <> 0 ) As Field2
FROM Table1

So the subquery functions as a data source for the single field 'Field2' in the main query.

 

[stone28]

thanks lagbolt,

I thought that is what I need when I saw your response.... Was so happy for a sec. ;-) The thing is it is calculating avg for the whole table not for each record in the bigger query.... I want this to be calculated for each movie in my case...
So show me all movies and show me what is the avg score for each of them. So you have long table of movie titles and then is some of them have some feedback left by users it calculated avg from it....

 

[stone28]

GOT IT!!!!!!! THANK YOU.... I just extended your query...... GREAT!!!!!!

SELECT Field1, ( SELECT AVG(Field1) FROM Table2 WHERE ((Field1 <> 0) AND table1.field1_id = table2.field1_id) ) As Field2
FROM Table1

PERFECT THANKS AGAIN....

 

[namliam]

This got me thinking, have you tried something like this?
SELECT Table1.Field1, Avg(IIf([Field2]=0,Null,[field2])) AS Expr1
FROM Table1
GROUP BY Table1.Field1;

Null values are not averaged, thus ignored...