What is the best way to do this..
A reader, May 28, 2004 - 7:11 pm UTC
DROP TABLE TEST;
CREATE TABLE TEST( DAY DATE, VOLUME NUMBER);
INSERT INTO TEST VALUES ( SYSDATE, 100);
INSERT INTO TEST VALUES ( SYSDATE -1, 0);
INSERT INTO TEST VALUES ( SYSDATE +1 , 200);
COMMIT;
For a given day , I need to select volume, only if the previous day volume and the current day volume are not zero. What is the best way to accomplish this.
May 28, 2004 - 8:15 pm UTC
what is the "current day volumne" if that is the same as the "given day" then
select volume
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 );
A reader, May 28, 2004 - 11:33 pm UTC
Dude
Current day is sysdate.
Previous day is sysdate -1
May 29, 2004 - 11:11 am UTC
dude to you, when statement starts with:
for a given day
and then goes into "current day", the question would be -- is given day current day, if not what then is given day.......
A reader, May 28, 2004 - 11:41 pm UTC
Tom Dude
How will the query look if we have a group by on date. Can we have an exists in the having clause.
May 29, 2004 - 11:12 am UTC
can you have an exists, sure.
no clue as to where group by would come into play for this table as the example is drawn out though.
having and exists
Lauren Hill, May 29, 2004 - 3:09 pm UTC
Interesting though,
TOM, I observe that it exists might not be very useful in the having clause. Instead why not just using the exists clause in the where, and just use the group by with out having.
In the given discussion
I guess what the reader is asking is
select day,sum(volume)
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
group by day
or this can be written as
select day,sum(volume)
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
group by day
Now instead doing the
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
in the where clause , how can we do it in the having clause after group by, and what is difference in both the approaches.
HAVING exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
Thanks
May 30, 2004 - 10:34 am UTC
(i've given up in most cases trying to "guess" what they mean.....)
but yes, the exists can be in either or location
A reader, June 01, 2004 - 11:35 am UTC
select volume
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 );
Tom, in the above case, the actual requirement is
Return the row if volume for either of the days i.e. :given_day or to_date( :given_day, 'format...' ) -1 is not equal to zero. Do not return the row, if both of them are zero.
In the query you wrote, the row will not be returned if volume for the :given_day is zero but the volume for the to_date( :given_day, 'format...' ) -1 is not equal to zero.
My solution is the following, please verify
select volume
from test
where day = to_date(:given_day, 'format....' )
and (volume <> 0 or volume = 0)
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
June 01, 2004 - 3:14 pm UTC
the query I wrote was for this requirement:
... only if the previous day volume and the current day volume are not zero. ....
just change my AND to an OR
select volume
from test
where day = to_date(:given_day, 'format....' )
and ( volume <> 0
OR
exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
)
How should we achieve the output we are looking for.
Otn, May 02, 2005 - 3:49 pm UTC
create table t ( start_date date, end_date date, value number)
/
insert into t values( '01-AUG-2005', '31-AUG-2005', 50)
/
insert into t values( '01-SEP-2005', '30-SEP-2005', 50)
/
Insert into t values( '01-OCT-2005', '31-OCT-2005', 60)
/
insert into t values( '01-NOV-2005', '30-NOV-2005', 50)
/
COMMIT
/
SQL> SET LINESIZE 200
SQL> SELECT * FROM T;
START_DAT END_DATE VALUE
--------- --------- ----------
01-AUG-05 31-AUG-05 50
01-SEP-05 30-SEP-05 50
01-OCT-05 31-OCT-05 60
01-NOV-05 30-NOV-05 50
The output we are looking for is
01-AUG-05 30-SEP-05 50
01-OCT-05 31-OCT-05 60
01-NOV-05 30-NOV-05 50
IF group by value the output is coming out as below, which is wrong because the first row shows a range which includes october.
01-AUG-05 - 30-NOV-05 50
01-OCT-05 - 31-OCT-05 60
IF grouped by begin and end date, it will return all the 4 rows, which we want to avoid.
How should we achieve the output we are looking for.
May 03, 2005 - 7:27 am UTC
see
https://www.oracle.com/technetwork/issue-archive/2014/14-mar/o24asktom-2147206.html analytics to the rescue (in 10g we could use last_value with ignore nulls instead of the max() trick to carry down)...
ops$tkyte@ORA9IR2> select min(start_date), max(end_date), min(value)
2 from (
3 select t.*, max(grp) over (order by start_date) grp2
4 from (
5 select t.*,
6 case when nvl(lag(value) over (order by start_date),value*-1) <> value
7 then row_number() over (order by start_date)
8 end grp
9 from t
10 ) t
11 )
12 group by grp2
13 order by 1;
MIN(START MAX(END_D MIN(VALUE)
--------- --------- ----------
01-AUG-05 30-SEP-05 50
01-OCT-05 31-OCT-05 60
01-NOV-05 30-NOV-05 50
A reader, May 09, 2005 - 11:42 am UTC
I have a scenario where the value will not be a number all the time, some times the value will be a string and other times the value will be a number. How should I handle that.
'Value' is the data you are seeing under the column value above. In the given example it is a number, but it has to be a varchar2 column.
Thanks in advance.
May 09, 2005 - 12:22 pm UTC
max works -- which one of the values from the range did you want to display??
value*-1
A reader, May 12, 2005 - 4:22 pm UTC
In the above sql what is the purpose of value*-1?
May 13, 2005 - 8:29 am UTC
when nvl(lag(value) over (order by start_date),value*-1) <> value
if the lag(value) is not null then we have:
when lag-value <> value
when we have lag-value being null we would have
when NULL <> value
which is not true or false, but we'd like false so
when -1*value <> value
assuming value (undocumented assumption on my part there) is not null we'd get "true"
To get previous day
A reader, May 20, 2005 - 3:01 pm UTC
Hi Tom,
If I do a
'select sysdate from dual;'
I am getting
SYSDATE
-------------------
05-20-2005 14:57:17
When I do 'select sysdate-1 from dual;'
I get
SYSDATE-1
-------------------
05-19-2005 14:57:22
What should I do if I want to get 05-19-2005 00:00:00
and 05-19-2005 23:59:59 as results.
Thanks.
May 20, 2005 - 6:44 pm UTC
select trunc(sysdate-1), trunc(sysdate)-1/24/60/60 from dual
Top 10 Analytical functions
A reader, March 11, 2007 - 6:47 pm UTC
Tom
Based on all the analytics questions you have answered on ASKTOM, what are the top 12 analytical functions, you have used.
Can you please list them out. Thanks in advance.
March 12, 2007 - 8:22 pm UTC
from a talk on analytics that I give:
LAG/LEAD (look back/forwards)
FIRST/LAST or MIN/MAX
ROW_NUMBER, RANK, DENSE_RANK, RATIO_TO_REPORT
AVG, SUM
NTILE
PERCENTILE_CONT/DISC
Great Answer. How to find the count(distinct) of column combination?
Prem, March 19, 2007 - 2:18 pm UTC
Tom,
Can I use the analytical function such as CUBE or ROLLUP to get the following?
create table t1 (col1 number, col3 number, col4 number, col6 number);
insert into t1 values (1, 10, 20, 30);
insert into t1 values (2, 20, 20, 30);
insert into t1 values (1, 20, 20, 20);
insert into t1 values (1, 10, 20, 30);
insert into t1 values (3, 30, 40, 60);
insert into t1 values (3, 30, 50, 50);
insert into t1 values (1, 10, 20, 30);
How would I find the count of distinct values of col1 alone, col1&col3 alone and col1&col3&col4 alone using a single query?
I would like to see the result as below (column title is not required),
col1 col1&col3 col1&col3&col4
------ -------------- --------------
3 4 5
col1 - I have three distinct values 1, 2, and 3
col1||col2 - I have 4 distinct values (1,10),(2,20), (1,20),(3,30)
Thank you!!!
March 19, 2007 - 3:43 pm UTC
ops$tkyte%ORA10GR2> select count(distinct col1) c1,
2 count(distinct col1||'/'||col3) c1c3,
3 count(distinct col1||'/'||col3||'/'||col4) c1c3c4
4 from t1;
C1 C1C3 C1C3C4
---------- ---------- ----------
3 4 5
Thank you!! I didn't think of the straight method
Prem, March 19, 2007 - 8:35 pm UTC
Thanks Tom for your response