Bernd Hohmann wrote:
Dear collegues,
small query problem.
A table (simplified example)...
customer as char(5)
inv_date as date
amount as double
stores the gross amount per invoice-date per customer.
select customer, dec(sum(amount) as Gross from orders
where inv_date between "2005-01-01" and "2005-12-31"
order by Gross desc
gives a sales volume list by custimer with the "biggest" customer at
top. (Customer, Gross)
Now I like to create a list with a comparision of the the sales volume
"current year - previous year" order by sales volume current year.
Customer / Sales 2005 / Sales 2004
Any idea how to sqeeze this into a single query?
Apart from the fact that your query is syntactically not correct, one idea
might be this:
SELECT o.customer,
( SELECT DEC(SUM(i.amount)) AS gross
FROM orders AS i
WHERE YEAR(i.inv_date) = YEAR(CURRENT DATE) AND
i.customer = o.customer ) AS this_year,
( SELECT DEC(SUM(i.amount)) AS gross
FROM orders AS i
WHERE YEAR(i.inv_date) = YEAR(CURRENT DATE) - 1 AND
i.customer = o.customer ) AS prev_year
FROM orders AS o
or this:
SELECT t1.customer, t1.amount, t2.amount
FROM ( SELECT customer, DEC(SUM(amount)) AS amount
FROM orders
WHERE YEAR(i.inv_date) = YEAR(CURRENT DATE)
GROUP BY customer ) AS t1 JOIN
( SELECT customer, DEC(SUM(amount)) AS amount
FROM orders
WHERE YEAR(i.inv_date) = YEAR(CURRENT DATE) - 1
GROUP BY customer ) AS t2 ON
( t1.customer = t2.customer )
Or via a single scan:
SELECT customer,
DEC(SUM(CASE
WHEN YEAR(i.inv_date) = YEAR(CURRENT DATE)
THEN amount
ELSE 0
END)),
DEC(SUM(CASE
WHEN YEAR(i.inv_date) = YEAR(CURRENT DATE) - 1
THEN amount
ELSE 0
END))
FROM orders
GROUP BY customer
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
