howzabout
SELECT thisyear , count( distinct currency )
FROM input_table
GROUP BY thisyear
HAVING count( distinct currency ) = 1
Fredrik Wahlgren wrote:
> Hi
>
> I have a table with a few columns like year, period, account,company,
> currency and so on. This table will be joined with one or several data
> tables, data00, data01
> In order to make a better main query, I want to select the years and the
> currency for which there is only one currency. Now, If i make a query like
> this
>
> SELECT DISTINCT thisyear, currency FROM input_table
>
> This is what I get
>
> thisyear currency
> 94 USD // Don't want this row, two currencies used for '94
> 94 GBP // Don't want this row, two currencies used for '94
> 95 EUR
> 96 EUR
> 97 EUR
> 98 EUR // Don't want this row, three currencies used for '98
> 98 SEK // Don't want this row, three currencies used for '98
> 98 USD // Don't want this row, three currencies used for '98
> 99 EUR
>
>
> I need both the year and the currency since I will save these in an array,
> The idea is to make a main query that looks like this. And , yes, this makes
> the query
> significantly faster
>
> SELECT this, that from data94 a, input_table b
> WHERE a.currency = b.currency AND ... //
> Non optimised condition
> UNION ALL SELECT this, that from data95 a, input_table b
> WHERE a.currency = 'EUR' AND ... //
> Optimised condition
> UNION ALL SELECT this, that from data96 a, input_table b
> WHERE a.currency = 'EUR' AND ...
>
> Thanks in advance,
> Fredrik
>
>
Received on Tue Jul 29 2003 - 16:03:04 CDT
