Oracle Database Express Edition (XE)

What do you mean by compare? Can you explain a bit more?

Select a.empcode, sum(a.totalvalue) , sum(b.totalvalue )
from table_sales a, table_sales b
where a.empcode = b.empcode
and a.trndate between ......
and b.trndate between ........
group by a.empcode

Edited by: Agustin UN on 08-nov-2010 13:22

Do you mean Sum(totalvalue) between those periods?

select 
Select sum(totalvalue) from table where trndate between to_date('01-10-2010', 'DD-MM-YYYY') AND  to_date('08-01-2010', 'DD-MM-YYYY')

SELECT ((SELECT SUM(totalvalue)
         FROM   table1
         WHERE  trndate BETWEEN to_date('01-10-2010', 'DD-MM-YYYY') AND
                to_date('08-10-2010', 'DD-MM-YYYY')) -
       (SELECT SUM(totalvalue)
         FROM   table1       
         WHERE  trndate BETWEEN to_date('01-11-2010', 'DD-MM-YYYY') AND
                to_date('01-11-2010', 'DD-MM-YYYY')))
FROM   dual

Be more clear on your requirement :)

I need some what like that, but there is a problem that if there is no sales in one range reports are not coming properly.

Please help

Thanks

Niloy

SELECT ((SELECT SUM(nvl(totalvalue,0))
         FROM   table1
         WHERE  trndate BETWEEN to_date('01-10-2010', 'DD-MM-YYYY') AND
                to_date('08-10-2010', 'DD-MM-YYYY')) -
       (SELECT SUM(nvl(totalvalue,0))
         FROM   table1       
         WHERE  trndate BETWEEN to_date('01-11-2010', 'DD-MM-YYYY') AND
                to_date('01-11-2010', 'DD-MM-YYYY')))
FROM   dual

I have table with the following data :

custcode emp Trndate Totalvalue
1 1 2010-10-01 500.00
2 1 2010-10-02 500.00
3 1 2010-10-03 500.00
4 1 2010-10-08 100.00
5 1 2010-11-02 175.00
6 5 2010-11-01 130.00
7 5 2010-11-02 135.00
8 5 2010-11-03 130.00
9 6 2010-10-01 100.00
10 6 2010-10-02 12.00


I'd like to build a query that returns something like this:

Empcode TotalSales_OCT TotalSales_NOV
====== ========= ===========
1 1600 175
5 0 395
6 112 0


Please help...

Thanks

Select a.empcode,b.empcode, sum(a.totalvalue) , sum(b.totalvalue )
from table_sales a, table_sales b
where a.empcode(+) = b.empcode
and a.trndate(+) between ......
and b.trndate(+) between ........
group by a.empcode
Union
Select a.empcode,b.empcode, sum(a.totalvalue) , sum(b.totalvalue )
from table_sales a, table_sales b
where a.empcode = b.empcode(+)
and a.trndate(+) between ......
and b.trndate(+) between ........
group by a.empcode

Edited by: Agustin UN on 08-nov-2010 13:50

with table_sales as (
select 1 custcode, 1 empcode, '2010-10-01' Trndate, 500.00 Totalvalue from dual union
select 2, 1, '2010-10-02', 500.00 from dual union
select 3, 1, '2010-10-03', 500.00 from dual union
select 4, 1, '2010-10-08', 100.00 from dual union
select 5, 1, '2010-11-02', 175.00 from dual union
select 6, 5, '2010-11-01', 130.00 from dual union
select 7 ,5, '2010-11-02', 135.00 from dual union
select 8, 5, '2010-11-03', 130.00 from dual union
select 9, 6, '2010-10-01', 100.00 from dual union
select 10, 6, '2010-10-02', 12.00 from dual
)
Select a.empcode, sum(a.totalvalue) , sum(b.totalvalue)
from table_sales a ,table_sales b
where
a.empcode = b.empcode(+)
and a.Trndate(+) between '2010-10-01' and '2010-10-31'
and b.Trndate(+) between '2010-11-01' and '2010-11-30'
group by a.empcode
union
Select b.empcode empcode, sum(a.totalvalue) , sum(b.totalvalue)
from table_sales a ,table_sales b
where
a.empcode(+) = b.empcode
and a.Trndate(+) between '2010-10-01' and '2010-10-31'
and b.Trndate(+) between '2010-11-01' and '2010-11-30'
group by b.empcode

In this case,Pivot clause is not useful :D

with table_sales(custcode,empcode,Trndate,Totalvalue) as(
select 1, 1,date '2010-10-01',500.00 from dual union
select 2, 1,date '2010-10-02',500.00 from dual union
select 3, 1,date '2010-10-03',500.00 from dual union
select 4, 1,date '2010-10-08',100.00 from dual union
select 5, 1,date '2010-11-02',175.00 from dual union
select 6, 5,date '2010-11-01',130.00 from dual union
select 7 ,5,date '2010-11-02',135.00 from dual union
select 8, 5,date '2010-11-03',130.00 from dual union
select 9, 6,date '2010-10-01',100.00 from dual union
select 10,6,date '2010-10-02', 12.00 from dual)
select empcode,
sum(case when Trndate
         between date '2010-10-01' and date '2010-10-08'
         then Totalvalue else 0 end) as OCT,
sum(case when Trndate
         between date '2010-11-01' and date '2010-11-08'
         then Totalvalue else 0 end) as NOV
  from table_sales
group by empcode
order by empcode;

EMPCODE   OCT  NOV
-------  ----  ---
      1  1600  175
      5     0  395
      6   112    0

SELECT ((SELECT SUM(nvl(totalvalue,0))
FROM table1
WHERE trndate BETWEEN to_date('01-10-2010', 'DD-MM-YYYY') AND
to_date('08-10-2010', 'DD-MM-YYYY') group by empcode) -
(SELECT SUM(nvl(totalvalue,0))
FROM table1
WHERE trndate BETWEEN to_date('01-11-2010', 'DD-MM-YYYY') AND
to_date('01-11-2010', 'DD-MM-YYYY') group by empcode))
FROM dual

Hi,
If I put this query I got the following error :

SELECT ((SELECT SUM(nvl(dispvalue,0))
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


Thanks
Niloy