2 count query results manipulation [message #634653] |
Thu, 12 March 2015 10:24 |
|
ketangarg86
Messages: 3 Registered: March 2015
|
Junior Member |
|
|
Hi,
I have a requirement to calculate the % change in the number of orders received today with the number of orders that were received 3 days back. All data is in the same table. There is a received date column.
I have two count(*) queries - one for today and one for 3 days back running separately and getting the results. Is it possible I can get the % change in orders received from 3 days back and today in one query.
Also if I want to get the number of orders received today between 12:00am today and current time. How would I modify the query.
Please help. I am relatively new to Oracle SQL.
Thanks
Ketan
|
|
|
|
Re: 2 count query results manipulation [message #634673 is a reply to message #634654] |
Fri, 13 March 2015 01:06 |
|
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
Hi,
I don't know urs exact requirement,But Please use the query with case statement like this :
select (same_date/days_3_back)*100 as percent_change
from(
select sum( case when order_dt = to_date(sysdate,'DD/MM/YYYY')
then 1
end
) as same_date,
sum( case when order_dt = to_date(sysdate - 3,'DD/MM/YYYY')
then 1
end
) as days_3_back
from source_table
)
[Updated on: Fri, 13 March 2015 01:06] Report message to a moderator
|
|
|
|
|
Re: 2 count query results manipulation [message #634698 is a reply to message #634653] |
Fri, 13 March 2015 07:38 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
This will compare each day order count to order count 3 days back:
with t as (
select count(*) over(order by trunc(order_dt) range between current row and current row) order_count_this_day,
count(*) over(order by trunc(order_dt) range between 3 preceding and 3 preceding) order_count_3_days_back
from source_table
)
select case
when order_count_3_days_back != 0 then order_count_this_day * 100 / order_count_3_days_back
end percent_change
from t
/
SY.
|
|
|