470,647 Members | 1,155 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

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 3384
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
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by Bob | last post: by
3 posts views Thread by UnixSlaxer | last post: by
2 posts views Thread by Marius Kaizerman | last post: by
3 posts views Thread by santoni | last post: by
9 posts views Thread by JJM0926 | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.