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?

On 1 Feb, 08:54, bmic...@gmail.com wrote:
> 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?

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

On Feb 1, 12:22 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On 1 Feb, 08:54, bmic...@gmail.com wrote:
>
>
>
> > 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?
>
> Are '-1' values actually values for which you have no data? If so they
> should be set as NULL.

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

On 1 Feb, 13:02, bmic...@gmail.com wrote:
> On Feb 1, 12:22 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On 1 Feb, 08:54, bmic...@gmail.com wrote:
>
> > > 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?
>
> > Are '-1' values actually values for which you have no data? If so they
> > should be set as NULL.
>
> That is correct.
> I have set them to '-1' though.

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.