SQL statement to sum rows, not columns

dbHell · #1 (**permalink**) 04-20-04, 11:19

Hi all,

I need to sum and average the values in each record so that I can use it to base my queries on.

ie:

I have a record with four values: 1, 3, 5, 7

The average is 4.

If I want to extract all records from a table where the average of these numbers is greater than 4, what would my statement look like?

Assume the values for the individual fields are: Value1, Value2, Value3, Value4. The table name is Total.

All built-in functions work on the columns over several records. My issue is different. I want something to work on several columns (fields) of one record.

Thanks in advance

Pat Phelan · #2 (**permalink**) 04-20-04, 11:30

With only the information you've provided, the best I can do is:

PHP Code:


			
SELECT *

   FROM totals

   WHERE  4 < (Coalesce(Value1, 0) + Coalesce(Value2, 0)

+    Coalesce(Value3, 0) + Coalesce(Value4, 0)) / 4

When working with columns within a row, you can easily derive the formulas for things like an average. The aggregate functions are provided for more difficult cases where you have arbitrary numbers of rows.

-PatP

joebednarz · #3 (**permalink**) 04-26-04, 16:21

Code:

SELECT * FROM total
WHERE ( value1 + value2 + value3 + value4 ) / 4 > 4;

JoeB

r937 · #4 (**permalink**) 04-26-04, 16:42

much prefer pat's solution

defensive sql

and guys, both of ya get minus 10 marks for the evil dreaded "select star"

Pat Phelan · #5 (**permalink**) 04-26-04, 22:00

Quote:

Originally Posted by r937

much prefer pat's solution

defensive sql

and guys, both of ya get minus 10 marks for the evil dreaded "select star"

Sorry, you can't take points away unless the specs contain enough information to avoid the problem. We only got the names of the columns of interest, no posted DDL, so we had to use * to ensure a complete answer!

-PatP

r937 · #6 (**permalink**) 04-26-04, 22:18

the preferred solution in that case is to use foo, bar, bat, qux and/or as many of those as necessary to make your point about the syntax

anything but the dreaded asterisk

Pat Phelan · #7 (**permalink**) 04-26-04, 22:28

Quote:

Originally Posted by r937

the preferred solution in that case is to use foo, bar, bat, qux and/or as many of those as necessary to make your point about the syntax

anything but the dreaded asterisk

Foo and bar I know all too well... Bat is a flying critter, or a file extension for a badly crippled scripting language. What the frog is a qux ???

-PatP