Using SQL to work out the average of some, but not all, records

Question

I am trying to work out the SQL to average a series of numbers, but only to include those greater than 0.

For example:

Field
4
5
0
6

SELECT Avg(Field) FROM Table;

gives 3.75. But I want it to ignore the 0 so the average is 5.

SELECT Avg(Field) FROM Table where Field > 0;

works but what if I have more than 1 field:

Field1   Field2
4        2 
5        0
0        3
6        4

SELECT Avg(Field1), Avg(Field2) FROM Table;

Using

SELECT Avg(Field1), Avg(Field2) FROM Table where Field1 >0 and Field2>0;

will not work

How do I do that? Note the numbers can be anything from 0 to 100

Will the fields always have same value? I mean: will Field1 be always 0 or 5? or it may be 0, 1, 2, 123 etc? — cyadvert
– cyadvert, Commented Mar 13, 2015 at 17:52

Fred · Accepted Answer · 2015-03-13 17:38:51Z

3

Just filter out 0 using the where clause.

SELECT Avg(Field) FROM Table Where Field > 0

answered Mar 13, 2015 at 17:38

Fred

5,8684 gold badges47 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RGriffiths Over a year ago

Thanks - can you see the supplementary. I was trying to keep the question simple.

Fred Over a year ago

@RichardGriffiths jpw beat me to that! :)

jpw · Accepted Answer · 2015-03-13 17:56:54Z

1

You can use a conditional expression:

SELECT 
  Avg(case when Field1 > 0 then Field1 end), 
  Avg(case when Field2 > 0 then Field2 end) 
FROM table

edited Mar 13, 2015 at 17:56

answered Mar 13, 2015 at 17:39

jpw

45k6 gold badges71 silver badges90 bronze badges

1 Comment

RGriffiths Over a year ago

Perfect - I have not come across that before. Thank you very much.

Khurram Ali · Accepted Answer · 2015-03-13 17:39:07Z

0

SELECT Avg(Field) FROM yourtable where Field > 0    ;

answered Mar 13, 2015 at 17:39

Khurram Ali

1,6795 gold badges20 silver badges39 bronze badges

1 Comment

RGriffiths Over a year ago

Thanks - can you see the supplementary. I was trying to keep the question simple.

cyadvert · Accepted Answer · 2015-03-13 17:59:20Z

0

You will have to fake AVG() :):):)

SELECT SUM(Field1)/SUM(IF(field1>0, 1, 0)), SUM(Field2)/SUM(IF(field2>0, 1, 0))

I assume there is GROUP BY in the query

answered Mar 13, 2015 at 17:59

cyadvert

8757 silver badges19 bronze badges

Comments

Jim Mc · Accepted Answer · 2015-03-13 18:23:03Z

0

Convert the 0 to null like this:

SELECT AVG(IF(N=0,NULL,N)) FROM
(
SELECT 5 N
UNION ALL SELECT 4 
UNION ALL SELECT 0 
UNION ALL SELECT 2

) the_ns

edited Mar 13, 2015 at 18:23

answered Mar 13, 2015 at 17:41

Jim Mc

3461 silver badge10 bronze badges

1 Comment

RGriffiths Over a year ago

That could work but I was trying to keep the question simple. They are not just 5s.

Collectives™ on Stack Overflow

Using SQL to work out the average of some, but not all, records

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related