Professional Web Applications Themes

SELECT average of everything - MySQL

The table (table1) stores the time and the currency values on each day. It looks like something like that: time USD JPY BGN CYP CZK DKK EEK 2007-01-05 1.3084 154.55 1.9558 0.5783 27.635 7.4532 15.6466 2007-01-04 1.3106 156.11 1.9558 0.5783 27.605 7.4531 -1 2007-01-03 1.3231 157.76 1.9558 0.5782 -1 7.4552 15.6466 2006-12-28 1.3173 156.61 1.9558 0.5782 27.54 -1 15.6466 What I want to do is get the yearly average of each of the currencies. That is, the result I want is something like that: year_time USD JPY BGN CYP CZK DKK EEK 2007 1.3140 156.14 1.9558 0.5782 27.62 7.4538 15.6466 ...

  1. #1

    Default SELECT average of everything

    The table (table1) stores the time and the currency values on each
    day. It looks like something like that:
    time USD JPY BGN CYP CZK DKK EEK
    2007-01-05 1.3084 154.55 1.9558 0.5783 27.635 7.4532 15.6466
    2007-01-04 1.3106 156.11 1.9558 0.5783 27.605 7.4531 -1
    2007-01-03 1.3231 157.76 1.9558 0.5782 -1 7.4552
    15.6466
    2006-12-28 1.3173 156.61 1.9558 0.5782 27.54 -1
    15.6466

    What I want to do is get the yearly average of each of the currencies.
    That is, the result I want is something like that:
    year_time USD JPY BGN CYP CZK DKK EEK
    2007 1.3140 156.14 1.9558 0.5782 27.62 7.4538 15.6466
    2006 1.3173 156.61 1.9558 0.5782 27.54
    -1 15.6466

    Conditions:
    - I don't want to include values that are '-1' in computing the
    average. If all for the year are '-1', the yearly average should be
    '-1' (as for DKK in 2006).
    - time available spans several years, not just 2007 and 2006
    - more columns with more currencies exist

    The closest I could get in the query is:
    SELECT year(time) AS year_time, AVG(USD), AVG(BGN), AVG(CYP),
    AVG(CZK), AVG(DKK), AVG(EEK)
    FROM table1
    WHERE USD <> '-1' AND JPY <> '-1' AND BGN <> '-1' AND CYP <> '-1' AND
    CZK <> '-1' AND DKK <> '-1' AND EEK <> '-1'
    GROUP BY year_time;

    The problem with this query is:
    - It is incorrect. Because it doesn't take into considerations the
    whole row 2007-01-04 (because CZK is '-1') and row 2007-01-03 (because
    EEK is '-1'). I would like to consider the values of those rows for
    all but CZK (in 2007-01-04) and EEK (in 2007-01-03) because their
    value is '-1'
    - It is inconvenient. Not only is the query very long but I don't
    actually know how many currency columns exist in the table. Some new
    columns might be added and I still want the query to execute
    correctly.

    Any help?

    bmichel@gmail.com Guest

  2. #2

    Default Re: SELECT average of everything

    On 1 Feb, 08:54, com wrote: 

    Are '-1' values actually values for which you have no data? If so they
    should be set as NULL.

    strawberry Guest

  3. #3

    Default Re: SELECT average of everything

    On Feb 1, 12:22 pm, "strawberry" <com> wrote: 





    >
    > Are '-1' values actually values for which you have no data? If so they
    > should be set as NULL.[/ref]

    That is correct.
    I have set them to '-1' though.

    bmichel@gmail.com Guest

  4. #4

    Default Re: SELECT average of everything

    On 1 Feb, 13:02, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > That is correct.
    > I have set them to '-1' though.[/ref]

    This is a structural error.

    Your table should probably look something like this

    rates(date*,currency*,rate)

    * = primary key

    grouping by date and currency, and averaging rates should give you the
    results that you want.

    strawberry Guest

Similar Threads

  1. average days
    By JoeyTMann in forum Macromedia ColdFusion
    Replies: 4
    Last Post: April 27th, 02:40 PM
  2. average
    By Lawrence in forum FileMaker
    Replies: 4
    Last Post: February 13th, 12:36 AM
  3. average using lingo
    By bright_lightsuk in forum Macromedia Director Lingo
    Replies: 0
    Last Post: November 11th, 11:59 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139