Home » SQL & PL/SQL » SQL & PL/SQL » DECODE and SUM query (Oracle 9i)
 DECODE and SUM query [message #550616] |
Wed, 11 April 2012 01:36  |
 |
ora_nov
Messages: 35
Registered: January 2012
|
Member |
|
|
Hi,
I have table test_1 like
CREATE TABLE test_1 (
src_page varchar2(150)
, ref_page varchar2(150)
, page_desc varchar2(100)
, no_hits NUMBER
)
;
And sample data
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'http://www.google.com', null, 1);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'http://www.google.com', 'Hello', 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'http://www.google.com', null, 1);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', null, null, 5);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', null, null, 1);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'http://www.google.com', 'Hello', 3);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'abcd', null, 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'abcc', null, 6);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', null, null, 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', null, null, 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'abcd', null, 6);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'http://www.google.com', 'Empty', 3);
INSERT INTO test_1 VALUES ('http://www1.ora_test_1.com', 'http://www.google.com', null, 5);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'http://www.google.com', null, 1);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'http://www.google.com', 'Hello', 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'http://www.google.com', null, 1);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', null, null, 5);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', null, null, 1);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'http://www.google.com', 'Hello', 3);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'abcd', null, 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'abcc', null, 6);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', null, null, 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', null, null, 2);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'abcd', null, 6);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'http://www.google.com', 'Empty', 3);
INSERT INTO test_1 VALUES ('http://www1.ora_test_2.com', 'http://www.google.com', null, 5);
Desired output:
src_page ref_page page_desc sum(no_hits)
http://www1.ora_test_1.com http://www.google.com Hello 20
http://www1.ora_test_1.com abcd null 8
http://www1.ora_test_1.com abcc null 6
http://www1.ora_test_1.com null null 10
http://www1.ora_test_2.com http://www.google.com Hello 20
http://www1.ora_test_2.com abcd null 8
http://www1.ora_test_2.com abcc null 6
http://www1.ora_test_2.com null null 10
For same src_page and ref_page, if page_desc is different it should take the value other than null and empty and update the page_desc, sum no_hits. In the above example it is Hello. So it should come as single row instead of 3 rows (If we do group by we get 3 rows).
TIA
[Updated on: Wed, 11 April 2012 01:43] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: DECODE and SUM query [message #550624 is a reply to message #550623] |
Wed, 11 April 2012 02:01  |
 |
Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Assuming there can be only 1 not null value:
SQL> col src_page format a26
SQL> col ref_page format a21
SQL> col page_desc format a10
SQL> with
2 data as (
3 select src_page, ref_page, no_hits,
4 last_value(nullif(page_desc,'Empty') ignore nulls)
5 over (partition by src_page, ref_page order by null) page_desc
6 from test_1
7 )
8 select src_page, ref_page, page_desc, sum(no_hits) no_hits
9 from data
10 group by src_page, ref_page, page_desc
11 order by src_page, ref_page, page_desc
12 /
SRC_PAGE REF_PAGE PAGE_DESC NO_HITS
-------------------------- --------------------- ---------- ----------
http://www1.ora_test_1.com abcc 6
http://www1.ora_test_1.com abcd 8
http://www1.ora_test_1.com http://www.google.com Hello 15
http://www1.ora_test_1.com 10
http://www1.ora_test_2.com abcc 6
http://www1.ora_test_2.com abcd 8
http://www1.ora_test_2.com http://www.google.com Hello 15
http://www1.ora_test_2.com 10
Regards
Michel
[Updated on: Wed, 11 April 2012 02:05] Report message to a moderator |
|
|
|
| Re: DECODE and SUM query [message #550629 is a reply to message #550624] |
Wed, 11 April 2012 02:35 |
 |
saipradyumn
Messages: 131
Registered: October 2011
Location: Hyderabad
|
Senior Member |
|
|
Hi Michel ,
Following is also one of the way for the same requirement ,
SQL> col src_page format a26
SQL> col ref_page format a21
SQL> col page_desc format a10
SQL>
SQL> with data as
2 (select t1.src_page,
3 t1.ref_page,
4 t1.page_desc,
5 sum(t1.no_hits) over(partition by t1.src_page, t1.ref_page
order by t1.src_page, t1.ref_page) sum,
6 row_number() over(partition by t1.src_page, t1.ref_page
order by decode(lower(page_desc), 'empty', null, page_desc)) row_order
7 from test_1 t1)
8 select t1.src_page, t1.ref_page, t1.page_desc, t1.sum
9 from data t1
10 where t1.row_order = 1
11 ;
SRC_PAGE REF_PAGE PAGE_DESC SUM
-------------------------- --------------------- ---------- ----------
http://www1.ora_test_1.com abcc 6
http://www1.ora_test_1.com abcd 8
http://www1.ora_test_1.com http://www.google.com Hello 15
http://www1.ora_test_1.com 10
http://www1.ora_test_2.com abcc 6
http://www1.ora_test_2.com abcd 8
http://www1.ora_test_2.com http://www.google.com Hello 15
http://www1.ora_test_2.com 10
8 rows selected
SQL>
Please let me know if i am wrong
Thanks
Sai Pradyumn
[Updated on: Wed, 11 April 2012 10:57] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 23 18:53:37 CDT 2013
Total time taken to generate the page: 0.78234 seconds
|
] 
Members
Search
Help
Register
Login
Home
