Home » SQL & PL/SQL » SQL & PL/SQL » SQL based on condition (11g)
SQL based on condition [message #384547] |
Wed, 04 February 2009 04:41  |
Bangla
Messages: 49 Registered: August 2008
|
Member |
|
|
My table is like below.
SQL> desc history;
Name Null? Type
-------------------- -------- --------------
ID NOT NULL NUMBER(32)
CDATE DATE
DESTNO VARCHAR2(50)
TIMESPENT NOT NULL NUMBER(32)
RATE NOT NULL NUMBER(32,6)
MONEYSPENT NOT NULL NUMBER(32,6)
COUNTRY VARCHAR2(50)
ACCTSESSIONID VARCHAR2(50)
Now I want to calculate the sum(timespent) and the sum(moneyspent) of only 1 day (between cdate and cdate-1).
The condition is that if within one day there is only one ACCTSESSIONID is found then summation will be done. If multiple ACCTSESSIONID found then sum will not be done. I tried like below,
select sum(moneyspent),sum(timespent) from history
where cdate between trunc(sysdate-1) and trunc(sysdate)
and 2<(select count(acctsessionid) from history
where cdate between trunc(sysdate-1) and trunc(sysdate));
But I failed. It returned all summation within one day.
|
|
|
|
|
|
Re: SQL based on condition [message #384554 is a reply to message #384547] |
Wed, 04 February 2009 05:08   |
Bangla
Messages: 49 Registered: August 2008
|
Member |
|
|
CREATE TABLE HISTORY
(
ID NUMBER(32) NOT NULL,
CDATE DATE,
DESTNO VARCHAR2(50 BYTE),
TIMESPENT NUMBER(32) NOT NULL,
MONEYSPENT NUMBER(32,6) NOT NULL,
ACCTSESSIONID VARCHAR2(50 BYTE)
);
insert into history values(1,to_date('04-02-09','DD-MM-YY'),'11',120,18.6,'89709');
insert into history values(2,to_date('04-02-09','DD-MM-YY'),'13',110,1.6,'89710');
insert into history values(3,to_date('04-02-09','DD-MM-YY'),'14',100,8.6,'89709');
insert into history values(4,to_date('04-02-09','DD-MM-YY'),'15',110,11.6,'89711');
insert into history values(5,to_date('04-02-09','DD-MM-YY'),'16',110,13.6,'89712');
See here ACCTSESSIONID 89709 is included twice , so rows contain 89709 will be excluded in summation. (2 rows in the above example).
[Updated on: Wed, 04 February 2009 05:09] Report message to a moderator
|
|
|
Re: SQL based on condition [message #384560 is a reply to message #384554] |
Wed, 04 February 2009 05:20   |
c_stenersen
Messages: 255 Registered: August 2007
|
Senior Member |
|
|
How about this? Does this help you?
select sum(moneyspent),sum(timespent)
from history H1
where cdate between trunc(sysdate-1) and trunc(sysdate)
and not exists(
select *
from history H2
where H2.id <> H1.id
and H2.acctsessionid = H1.acctsessionid
and cdate between trunc(sysdate-1) and trunc(sysdate));
Cheers,
C.
|
|
|
Re: SQL based on condition [message #384567 is a reply to message #384554] |
Wed, 04 February 2009 05:30   |
Martin Eysackers
Messages: 80 Registered: October 2005 Location: Belgium
|
Member |
|
|
this will do the trick as well and only access history once :
select sum(m),sum(t)
from
(
select sum(moneyspent) m,sum(timespent) t from history
where cdate between trunc(sysdate-1) and trunc(sysdate)
group by acctsessionid
having count(acctsessionid) = 1
)
|
|
|
|
Re: SQL based on condition [message #384723 is a reply to message #384547] |
Thu, 05 February 2009 00:02   |
Bangla
Messages: 49 Registered: August 2008
|
Member |
|
|
CREATE TABLE HISTORY
(
ID NUMBER(32) NOT NULL,
CDATE DATE,
DESTNO VARCHAR2(50 BYTE),
TIMESPENT NUMBER(32) NOT NULL,
MONEYSPENT NUMBER(32,6) NOT NULL,
ACCTSESSIONID VARCHAR2(50 BYTE)
);
insert into history values(1,to_date('04-02-09','DD-MM-YY'),'11',120,18.6,'89709');
insert into history values(2,to_date('04-02-09','DD-MM-YY'),'13',110,1.6,'89710');
insert into history values(3,to_date('04-02-09','DD-MM-YY'),'14',100,8.6,'89709');
insert into history values(4,to_date('04-02-09','DD-MM-YY'),'15',110,11.6,'89711');
insert into history values(5,to_date('04-02-09','DD-MM-YY'),'16',110,13.6,'89712');
Here I only want the lower value of id's moneyspent if they have count =2 of acctsessionid.
For example 89709 acctsessionid comes, hence I only want lower value id between 11 and 14, that is 11's moneyspent which is 18.6
|
|
|
|
Re: SQL based on condition [message #384748 is a reply to message #384723] |
Thu, 05 February 2009 00:54  |
idris.ali
Messages: 34 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
If you want to consider just one of the two repeated acctsessionid
with minimum id ..try this
SELECT SUM(timespent),
SUM(moneyspent)
FROM history h
WHERE cdate BETWEEN Trunc(SYSDATE - 1) AND Trunc(SYSDATE + 1)
AND id = (SELECT MIN(id)
FROM history h1
WHERE h.acctsessionid = h1.acctsessionid)
|
|
|
Goto Forum:
Current Time: Tue Feb 18 00:33:37 CST 2025
|