Home » SQL & PL/SQL » SQL & PL/SQL » SQL based on condition (11g)
SQL based on condition [message #384547] Wed, 04 February 2009 04:41 Go to next message
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 #384548 is a reply to message #384547] Wed, 04 February 2009 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Describe in words what this does:
and 2<(select count(acctsessionid) from history
 where cdate between trunc(sysdate-1) and trunc(sysdate));
Re: SQL based on condition [message #384552 is a reply to message #384547] Wed, 04 February 2009 04:58 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
It is wrong. I wanted,
if count(acctsessionid) =1 then that row will be counted as summation.
Otherwise it will not be counted.
Re: SQL based on condition [message #384553 is a reply to message #384552] Wed, 04 February 2009 05:01 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Can you provide DML and DDL statements for your query, may be then we can help better.
Re: SQL based on condition [message #384554 is a reply to message #384547] Wed, 04 February 2009 05:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #384568 is a reply to message #384547] Wed, 04 February 2009 05:32 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
Yah, I did this.
Re: SQL based on condition [message #384723 is a reply to message #384547] Thu, 05 February 2009 00:02 Go to previous messageGo to next message
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 #384745 is a reply to message #384723] Thu, 05 February 2009 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: SQL based on condition [message #384748 is a reply to message #384723] Thu, 05 February 2009 00:54 Go to previous message
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) 
Previous Topic: problem in date conversion (merged)
Next Topic: select value that doesn;t match teh outerjoin constant
Goto Forum:
  


Current Time: Fri Dec 09 21:33:39 CST 2016

Total time taken to generate the page: 0.04887 seconds