How to calculate in percent in sql query? [message #631364] |
Tue, 13 January 2015 02:18 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
Select count(total ms1) MS1 from A where launch_date = trunc(sysdate)
union all
Select count(total ms2)) MS2 from A where launch_date = trunc(sysdate -1)
I want out put like
MS1 MS2 Diff with (MS1 - MS2)
1000 400 600
But I need Diff with (MS1 - MS2) in percent not number.
[Updated on: Tue, 13 January 2015 02:20] Report message to a moderator
|
|
|
|
|
|
|
|
Re: How to calculate in percent in sql query? [message #631406 is a reply to message #631376] |
Tue, 13 January 2015 10:17 |
Solomon Yakobson
Messages: 3267 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
aaditya321 wrote on Tue, 13 January 2015 04:32I am confuse here caused ms1 hold sysdate data & ms2 hold sysdate -1 data.
Which is bad since you read same table twice. Use something like:
with t as (
select case
when abs((count(*) - lead(count(*)) over(order by launch_date desc)) / count(*)) > .95 then 'alert'
end alert
from A
where launch_date in (trunc(sysdate),trunc(sysdate - 1))
group by launch_date
)
select alert
from t
where alert is not null
/
For example:
SQL> with a as (
2 select trunc(sysdate) launch_date
3 from dual
4 connect by level <= 5
5 union all
6 select trunc(sysdate - 1) launch_date
7 from dual
8 connect by level <= 7
9 ),
10 t as (
11 select case
12 when abs((count(*) - lead(count(*)) over(order by launch_date desc)) / count(*)) > .95 then 'alert'
13 end alert
14 from A
15 where launch_date in (trunc(sysdate),trunc(sysdate - 1))
16 group by launch_date
17 )
18 select alert
19 from t
20 where alert is not null
21 /
no rows selected
SQL> with a as (
2 select trunc(sysdate) launch_date
3 from dual
4 connect by level <= 5
5 union all
6 select trunc(sysdate - 1) launch_date
7 from dual
8 connect by level <= 10
9 ),
10 t as (
11 select case
12 when abs((count(*) - lead(count(*)) over(order by launch_date desc)) / count(*)) > .95 then 'alert'
13 end alert
14 from A
15 where launch_date in (trunc(sysdate),trunc(sysdate - 1))
16 group by launch_date
17 )
18 select alert
19 from t
20 where alert is not null
21 /
ALERT
-----
alert
SQL>
SY.
|
|
|
|
|
Re: How to calculate in percent in sql query? [message #631410 is a reply to message #631409] |
Tue, 13 January 2015 11:37 |
|
Michel Cadot
Messages: 68619 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
And?
You didn't have enough information including queries to lead you to your query?
I remind you the rules:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Use SQL*Plus and copy and paste what you already tried.
[Updated on: Tue, 13 January 2015 11:38] Report message to a moderator
|
|
|
Re: How to calculate in percent in sql query? [message #631443 is a reply to message #631410] |
Wed, 14 January 2015 01:56 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
Now I want to difference between current date & any other date, like sysdate & sysdate-1 or sysdate & sysdate-2 or like more..
and need difference in percent.
create table Diff(id number(4),
ms number(6),
date Date);
Insert into Diff(id , ms, date)
value(01, 10002, 'sysdate');
Insert into Diff(id , ms, date)
value(02, 10003, 'sysdate');
Insert into Diff(id , ms, date)
value(03, 10004, 'sysdate');
Insert into Diff(id , ms, date)
value(04, 10005, 'sysdate');
Insert into Diff(id , ms, date)
value(01, 10000, 'sysdate-1');
Insert into Diff(id , ms, date)
value(02, 10001, 'sysdate-1');
Insert into Diff(id , ms, date)
value(03, 10002, 'sysdate-1');
Insert into Diff(id , ms, date)
value(04, 10000, 'sysdate-1');
Insert into Diff(id , ms, date)
value(01, 10003, 'sysdate-2');
Insert into Diff(id , ms, date)
value(02, 10004, 'sysdate-2');
Insert into Diff(id , ms, date)
value(03, 10005, 'sysdate-2');
Insert into Diff(id , ms, date)
value(04, 10006, 'sysdate-2');
|
|
|
|
|
|
|
|
Re: How to calculate in percent in sql query? [message #631468 is a reply to message #631464] |
Wed, 14 January 2015 02:49 |
cookiemonster
Messages: 13915 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
aaditya321 wrote on Wed, 14 January 2015 08:45Hello Cookiemonster,
Please write query here.
Are you really saying you need me to rewrite the above query to replace sysdate with a variable?
If you can't manage that you're in the wrong job.
|
|
|
Re: How to calculate in percent in sql query? [message #631469 is a reply to message #631468] |
Wed, 14 January 2015 02:54 |
|
aaditya321
Messages: 225 Registered: January 2014 Location: Delhi
|
Senior Member |
|
|
ID ms of sysdate ms of sysdate-1 Diff. of ms between two date Percent of Diff. in two date
01 1001 1000 1 %.1
I want output like above.
or
if some one say to calculate sysdate & sysdate-2 diff. then output like below
ID ms of sysdate ms of sysdate-2 Diff. of ms between two date Percent of Diff. in two date
01 1002 1000 2 %.2
[Updated on: Wed, 14 January 2015 02:57] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to calculate in percent in sql query? [message #631560 is a reply to message #631559] |
Thu, 15 January 2015 06:21 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
aaditya321 wrote on Thu, 15 January 2015 17:46one more query here, please give me guide.
Suppose today(current date)publish English Book then now we have to find out when published English book prior current date.
Michel Cadot wrote on Tue, 13 January 2015 23:07
I remind you the rules:
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Use SQL*Plus and copy and paste what you already tried.
And this seems to be a different question, please open a new topic.
|
|
|