Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Select value of most occurances - analytical function?!

777489Jan 14 2011 — edited Jan 31 2011
Hi ...

I've get stuck with a "little" problem.
I try to provide some testing code for this: 
CREATE TABLE a1 ( 
	id NUMBER(8), 
	val NUMBER(6),
	title VARCHAR2(16),
	CONSTRAINT test_pk PRIMARY KEY(id)
);
INERT INTO a1 (id, val, title) VALUES (1,12,'c');
INERT INTO a1 (id, val, title) VALUES (2,13,'b');
INERT INTO a1 (id, val, title) VALUES (3,13,'a');
INERT INTO a1 (id, val, title) VALUES (4,13,'a');
INERT INTO a1 (id, val, title) VALUES (5,42,'a');
INERT INTO a1 (id, val, title) VALUES (6,42,'b');
INERT INTO a1 (id, val, title) VALUES (7,42,'b');
Actually the table is much bigger ;) But this should be ok for this question. It already exist a query like: 
SELECT
	count(*) -- just an example
FROM 
	a1
GROUP BY
	val
;
-- should return 1,3,3 (for the groups val=12, val=13,val=42)
Now it is nessecary to select a title for each group (specified by group by). And there the title which occurs the most in this group should be selected. For this example this are 'c' for the group val=12 .. 'a' for the group val=13 and finally 'b' for the group val=42.
I tried to use some anayltical function, but I#m not able to get this to work - may be because I never used analytical functions before. If I try something I mostly get an error: Keyword FROM not at expected position (ORA-00923). I searched for some tutorial/howto documentations where my problem is handled but without success. So I guess the syntax and the way to understand analytical functions is not as easy as it semms to be ...
title OVER ( ORDER BY count(*) ROWS | RANGE BETWEEN 1 AND 1 ) <-- that would by logical for my brain, but not for oracles ;-)
Can somebody help?

Thanks!

Comments

Karthick2003
What do you mean by compare? Can you explain a bit more?
Dbb
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
726742
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 :)
787094
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
726742 
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
787094
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
Dbb
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
Dbb
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
Aketi Jyuuzou
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
ramanlakunde
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
787094
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
1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 28 2011
Added on Jan 14 2011
26 comments
16,559 views