Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: SQL statement to sum rows, not columns

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    With only the information you've provided, the best I can do is:
    PHP Code:
    SELECT *
       
    FROM totals
       WHERE  4 
    < (Coalesce(Value10) + Coalesce(Value20)
    +    
    Coalesce(Value30) + Coalesce(Value40)) / 
    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

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Code:
    SELECT * FROM total
    WHERE ( value1 + value2 + value3 + value4 ) / 4 > 4;
    JoeB

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    much prefer pat's solution

    defensive sql

    and guys, both of ya get minus 10 marks for the evil dreaded "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there's a whole flock of 'em

    i had bat where i shoulda had baz

    foo is
    First on the standard list of metasyntactic variables used in syntax examples. See also bar, baz, qux, quux, garply, waldo, fred, plugh, xyzzy, thud.
    Last edited by r937; 04-26-04 at 23:41.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Well I'll be furshlurgled (to borrow one from Don Martin)!

    I done has learnt sumpin' Now I are gonna go to home!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •