I currently have a table as follows:

order_date, order_no, total_amt, currency

What I would like to do is to generate a query which gives me the
total daily amount for each currency in a given date range, on a
day-by-day basis. So, the resulting table will look like:

order_date (sum(USD)) (sum(EUR)) (sum(GBP))

where I will know all the currency codes in advance. The best I could
come up with was the following:

select order_date,
(select sum(total_amt) from orders as dollar
where currency = 'USD' and dollar.order_date = orders.order_date) as
dollarttl,
(select sum(total_amt) from orders as euro
where currency = 'EUR' and euro.order_date = orders.order_date) as
eurottl
from orders
where order_date > 01/01/03 and order_date <= 01/31/03

This query is horribly slow however. Is there a better way to achieve
the same result?

Thanks,
Mike

What's wrong with a crosstab query where order-date is in rows and currency
is in columns? Group by order_date, currency. Specify total_amt as value and
sum it.

Have criterion on order_date 'between 1/1/03 and 1/31/03'. Note that using
the 'between' clause *includes* the specified dates. The way you have it in
your example, you are excluding 1/1/03.

"Mike" <mluffman (AT) barcodehq (DOT) com> wrote