Home » SQL & PL/SQL » SQL & PL/SQL » 2 count query results manipulation
2 count query results manipulation [message #634653] Thu, 12 March 2015 10:24 Go to next message
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 #634654 is a reply to message #634653] Thu, 12 March 2015 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


can you write SQL when you don't know table name or column names?
Re: 2 count query results manipulation [message #634673 is a reply to message #634654] Fri, 13 March 2015 01:06 Go to previous messageGo to next message
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 #634681 is a reply to message #634673] Fri, 13 March 2015 01:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
msol25 wrote on Fri, 13 March 2015 11:36
order_dt = to_date(sysdate,'DD/MM/YYYY')


This makes no sense. What is the purpose of converting DATE into DATE?
Re: 2 count query results manipulation [message #634682 is a reply to message #634673] Fri, 13 March 2015 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
order_dt = to_date(sysdate,'DD/MM/YYYY')


This completely silly... and wrong:
SQL> select to_date(sysdate,'DD/MM/YYYY') from dual;
select to_date(sysdate,'DD/MM/YYYY') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string



Re: 2 count query results manipulation [message #634698 is a reply to message #634653] Fri, 13 March 2015 07:38 Go to previous message
Solomon Yakobson
Messages: 3312
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.
Previous Topic: Reference Partitioning
Next Topic: ORA-01427: single-row subquery returns more than one row
Goto Forum:
  


Current Time: Wed Aug 26 07:39:53 CDT 2026