473,692 Members | 2,103 Online

# Query problem: sales volume comparision with previous year

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
Dec 29 '05 #1
2 3561
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.amoun t)) 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.amoun t)) 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
Dec 29 '05 #2
Knut Stolze wrote:
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
Dec 29 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.