Query problem: sales volume comparision with previous year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bernd Hohmann - 29 Dec 2005 21:20 GMT

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?

Bernd

Reply to this Message

Knut Stolze - 29 Dec 2005 21:37 GMT

> Dear collegues,
>
[quoted text clipped - 21 lines]
>
> 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

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Bernd Hohmann - 29 Dec 2005 22:56 GMT

>> 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:

Well, SQL isn't my mother tongue :-)

Thanks for your help, I'll try to transform your examples into the final
query and query visual explain how the're performing.

Bernd

Reply to this Message