Home » SQL & PL/SQL » SQL & PL/SQL » How to display report format data (Oracle 11)
|
Re: How to display report format data [message #653259 is a reply to message #653258] |
Thu, 30 June 2016 08:13 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
What should result be?
prompt Importing table devicewise_bucket...
set feedback off
set define off
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027647, 'ifood.tv', '728x90', 'us', 'unknown', 'Mobile', 7848);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027647, 'ifood.tv', '728x90', 'us', 'unknown', 'Tablet', 722);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '120x600', 'us', 'missing_rubicon_seq0', 'Desktop', 50);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'ca', 'direct_monetized', 'Desktop', 2);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'direct_monetized', 'Desktop', 44);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'direct_monetized', 'Tablet', 15);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'error', 'Desktop', 1);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'floor_not_met', 'Mobile', 4);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'floor_not_met', 'Tablet', 1);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'missing_rubicon_seq0', 'Desktop', 38);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'missing_rubicon_seq0', 'Mobile', 5);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'missing_rubicon_seq0', 'Tablet', 3);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'no_buy_on_inventory', 'Desktop', 3);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'pax_monetized', 'Desktop', 33);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'pax_monetized', 'Mobile', 1);
insert into devicewise_bucket (DATE_ID, AFFILIATE_ID, CHANNEL_NAME, AD_SIZE_ID, COUNTRY_ID, BUCKET_TYPE, DEVICE_TYPE, IMPRESSIONS)
values (20160620, 370027657, 'Sacred Bombshell', '160x600', 'us', 'pax_monetized', 'Tablet', 3);
prompt Done.
|
|
|
|
|
|
Re: How to display report format data [message #653270 is a reply to message #653258] |
Thu, 30 June 2016 11:23 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
I have to display Bucket_type
wise Report with Top 10 Affiliate_id as col.and Bucket_type as col and Device_type wise
Bucket_type Floor_nt_met
Device_type
Mobile Desktop Tablet Grand_total
1 2 3 4
2 30 56 656
3 39 56 56
4 23 10 45
5 45 56
6
7
8
9
10
This is just Format I have to show Top 10 Affilates who is having larger no. Sum(Impressions)for each Bucket_type and then Device_type for each Bucket as well.
[mod-edit: code tags added by bb]
[Updated on: Thu, 30 June 2016 17:22] by Moderator Report message to a moderator
|
|
|
|
Re: How to display report format data [message #653273 is a reply to message #653272] |
Thu, 30 June 2016 13:42 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Even if we want to help, there is no way to do so because we do not know your database version, We do not have a table create statement, and we don't have a example of what you want to display with it explained out in clear requirements. Please give us what I asked for and we will try to help. To get the version type
select * from v$version;
in sqlplus and past what it displays into this issue.
[Updated on: Thu, 30 June 2016 13:45] Report message to a moderator
|
|
|
Re: How to display report format data [message #653283 is a reply to message #653270] |
Thu, 30 June 2016 17:58 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your description is unclear and your sample data does not seem to match your output.
I gather that you have a table created something like:
create table devicewise_bucket
(date_id number,
affiliate_id number,
channel_name varchar2(16),
ad_size_id varchar2(10),
country_id varchar2(10),
bucket_type varchar2(20),
device_type varchar2(11),
impressions number)
/
It would have helped if you provide such a create table statement or at least a describe of the table, like this:
SCOTT@orcl_12.1.0.2.0> describe devicewise_bucket
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
DATE_ID NUMBER
AFFILIATE_ID NUMBER
CHANNEL_NAME VARCHAR2(16)
AD_SIZE_ID VARCHAR2(10)
COUNTRY_ID VARCHAR2(10)
BUCKET_TYPE VARCHAR2(20)
DEVICE_TYPE VARCHAR2(11)
IMPRESSIONS NUMBER
After creating the table and loading the data using the inserts that you provided, I get:
SCOTT@orcl_12.1.0.2.0> select * from devicewise_bucket order by affiliate_id, bucket_type, device_type, impressions
2 /
DATE_ID AFFILIATE_ID CHANNEL_NAME AD_SIZE_ID COUNTRY_ID BUCKET_TYPE DEVICE_TYPE IMPRESSIONS
---------- ------------ ---------------- ---------- ---------- -------------------- ----------- -----------
20160620 370027647 ifood.tv 728x90 us unknown Mobile 7848
20160620 370027647 ifood.tv 728x90 us unknown Tablet 722
20160620 370027657 Sacred Bombshell 160x600 ca direct_monetized Desktop 2
20160620 370027657 Sacred Bombshell 160x600 us direct_monetized Desktop 44
20160620 370027657 Sacred Bombshell 160x600 us direct_monetized Tablet 15
20160620 370027657 Sacred Bombshell 160x600 us error Desktop 1
20160620 370027657 Sacred Bombshell 160x600 us floor_not_met Mobile 4
20160620 370027657 Sacred Bombshell 160x600 us floor_not_met Tablet 1
20160620 370027657 Sacred Bombshell 160x600 us missing_rubicon_seq0 Desktop 38
20160620 370027657 Sacred Bombshell 120x600 us missing_rubicon_seq0 Desktop 50
20160620 370027657 Sacred Bombshell 160x600 us missing_rubicon_seq0 Mobile 5
20160620 370027657 Sacred Bombshell 160x600 us missing_rubicon_seq0 Tablet 3
20160620 370027657 Sacred Bombshell 160x600 us no_buy_on_inventory Desktop 3
20160620 370027657 Sacred Bombshell 160x600 us pax_monetized Desktop 33
20160620 370027657 Sacred Bombshell 160x600 us pax_monetized Mobile 1
20160620 370027657 Sacred Bombshell 160x600 us pax_monetized Tablet 3
16 rows selected.
It looks like you want to pivot the data, and get the 10 rows with the greatest grand_total, something like this:
SCOTT@orcl_12.1.0.2.0> select *
2 from (select affiliate_id, bucket_type,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total
7 from devicewise_bucket
8 group by affiliate_id, bucket_type
9 order by grand_total desc)
10 where rownum <= 10
11 /
AFFILIATE_ID BUCKET_TYPE MOBILE DESKTOP TABLET GRAND_TOTAL
------------ -------------------- ---------- ---------- ---------- -----------
370027647 unknown 7848 0 722 8570
370027657 missing_rubicon_seq0 5 88 3 96
370027657 direct_monetized 0 46 15 61
370027657 pax_monetized 1 33 3 37
370027657 floor_not_met 4 0 1 5
370027657 no_buy_on_inventory 0 3 0 3
370027657 error 0 1 0 1
7 rows selected.
You can format that using SQL*Plus commands to turn it into whatever report that you want.
That should be enough to get you started. If you need more help, then you need to provide a better explanation and results that match your sample data.
|
|
|
|
|
|
Re: How to display report format data [message #653291 is a reply to message #653288] |
Fri, 01 July 2016 01:04 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
I have to display this type of data for 3 Bucket_type.For Top 10 Affialiates is it possible using single query.
I have used following query but it shows only for 1 bucket_type at a time.
select *
from (select affiliate_id, bucket_type,
sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
sum (impressions) grand_total
from devicewise_bucket where bucket_type='direct_monetized'----api_2_0
group by affiliate_id, bucket_type
order by grand_total desc)
where rownum <= 10;
O/p of the query:
BUCKET_TYPE floor_not_met BUCKET_TYPE direct_monetized
Device_type Device_type
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
1801801646 5531724 1 944 5532669 1 1674217084 74008 12015013 53377 12142398
1763489660 1488217 58 14438 1502713 2 43714499 1773593 1632130 270136 3675859
43714499 898206 7689 3173 909068 3 1701207318 9821 3093269 164 3103254
1652235953 646057 2135 24634 672826 4 1670206010 398 2609941 9 2610348
1701207318 67468 510937 20 578425 5 1726481576 1114533 1197062 129975 2441570
1823766970 541976 2852 5364 550192 6 1676072358 409846 1252751 461357 2123954
356682851 420721 362 3610 424693 7 8156650 562429 1310826 208517 2081772
348475431 313769 6932 14521 335222 8 1692374378 7783 1234451 21600 1263834
8156650 235864 21202 11732 268798 9 1763489660 1120597 845 134015 1255457
1681530432 0 219054 0 219054 10 1853843081 10 1154051 460 1154521
[mod-edit: code tags added by bb]
[Updated on: Fri, 01 July 2016 02:33] by Moderator Report message to a moderator
|
|
|
Re: How to display report format data [message #653294 is a reply to message #653291] |
Fri, 01 July 2016 01:24 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Fri, 01 July 2016 07:55
And we want you answer our questions and post what we request.
Plz. Help
Thanks In Adanvce
BlackSwan wrote on Thu, 30 June 2016 15:13
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
Michel Cadot wrote on Thu, 30 June 2016 18:47
Quote:This is just Format
And do you think what you posted is formatted?
So you want we format a query result in the form you did not format, doesn't it?
What is "Floor_nt_met"?
What is "Grand_total"?
Bill B wrote on Thu, 30 June 2016 20:42Even if we want to help, there is no way to do so because we do not know your database version, We do not have a table create statement, and we don't have a example of what you want to display with it explained out in clear requirements. Please give us what I asked for and we will try to help. To get the version type
select * from v$version;
in sqlplus and past what it displays into this issue.
|
|
|
|
|
Re: How to display report format data [message #653299 is a reply to message #653291] |
Fri, 01 July 2016 03:09 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Since I don't have your actual data, my results will be different and there will only be one row of data per bucket_type, since there is only one affiliate.
Are you using SQL*Plus or some other reporting tool?
If you are using some other reporting tool and just need the query, then:
SCOTT@orcl_12.1.0.2.0> select bucket_type, affiliate_id, mobile, desktop, tablet, grand_total
2 from (select bucket_type, affiliate_id,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total,
7 row_number () over
8 (partition by bucket_type
9 order by sum (impressions) desc) rn
10 from devicewise_bucket
11 group by bucket_type, affiliate_id
12 order by bucket_type, affiliate_id, grand_total desc)
13 where rn <= 10
14 order by bucket_type, affiliate_id, grand_total desc
15 /
BUCKET_TYPE AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
-------------------- ------------ ---------- ---------- ---------- -----------
direct_monetized 370027657 0 46 15 61
error 370027657 0 1 0 1
floor_not_met 370027657 4 0 1 5
missing_rubicon_seq0 370027657 5 88 3 96
no_buy_on_inventory 370027657 0 3 0 3
pax_monetized 370027657 1 33 3 37
unknown 370027647 7848 0 722 8570
If you are using SQL*Plus, then you can add some formatting like below to the query above.
SCOTT@orcl_12.1.0.2.0> break on bucket_type skip page
SCOTT@orcl_12.1.0.2.0> column bucket_type new_value bucketvar noprint
SCOTT@orcl_12.1.0.2.0> ttitle left 'BUCKET_TYPE ' bucketvar
SCOTT@orcl_12.1.0.2.0> column mobile heading "DEVICE_TYPE|MOBILE"
SCOTT@orcl_12.1.0.2.0> select bucket_type, affiliate_id, mobile, desktop, tablet, grand_total
2 from (select bucket_type, affiliate_id,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total,
7 row_number () over
8 (partition by bucket_type
9 order by sum (impressions) desc) rn
10 from devicewise_bucket
11 group by bucket_type, affiliate_id
12 order by bucket_type, affiliate_id, grand_total desc)
13 where rn <= 10
14 order by bucket_type, affiliate_id, grand_total desc
15 /
BUCKET_TYPE direct_monetized
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 0 46 15 61
BUCKET_TYPE error
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 0 1 0 1
BUCKET_TYPE floor_not_met
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 4 0 1 5
BUCKET_TYPE missing_rubicon_seq0
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 5 88 3 96
BUCKET_TYPE no_buy_on_inventory
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 0 3 0 3
BUCKET_TYPE pax_monetized
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 1 33 3 37
BUCKET_TYPE unknown
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027647 7848 0 722 8570
7 rows selected.
|
|
|
|
|
|
Re: How to display report format data [message #653325 is a reply to message #653304] |
Fri, 01 July 2016 15:19 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
ssyr22 wrote on Fri, 01 July 2016 02:20If I have to dispaly only for 2 bucket_type for 'Floor_nt_met' and 'api_2.0' in sepearte and reamiing I have to display as total for 'Catch_all'
What changes I hae to do in this?
There is no api_2.0 in the data that you provided, so that is not included in the results below. Notice the addition of the innermost select using decode and those values.
-- query alone:
SCOTT@orcl_12.1.0.2.0> select bucket_type, affiliate_id, mobile, desktop, tablet, grand_total
2 from (select bucket_type, affiliate_id,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total,
7 row_number () over
8 (partition by bucket_type
9 order by sum (impressions) desc) rn
10 from (select decode
11 (lower (bucket_type),
12 'floor_not_met', bucket_type,
13 'api_2.0', bucket_type,
14 'Catch_all') as bucket_type,
15 affiliate_id, device_type, impressions
16 from devicewise_bucket)
17 group by bucket_type, affiliate_id
18 order by bucket_type, affiliate_id, grand_total desc)
19 where rn <= 10
20 order by bucket_type, affiliate_id, grand_total desc
21 /
BUCKET_TYPE AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
-------------------- ------------ ---------- ---------- ---------- -----------
Catch_all 370027647 7848 0 722 8570
Catch_all 370027657 6 171 21 198
floor_not_met 370027657 4 0 1 5
3 rows selected.
-- query with SQL*Plus formatting:
SCOTT@orcl_12.1.0.2.0> break on bucket_type skip page
SCOTT@orcl_12.1.0.2.0> column bucket_type new_value bucketvar noprint
SCOTT@orcl_12.1.0.2.0> ttitle left 'BUCKET_TYPE ' bucketvar
SCOTT@orcl_12.1.0.2.0> column mobile heading "DEVICE_TYPE|MOBILE"
SCOTT@orcl_12.1.0.2.0> select bucket_type, affiliate_id, mobile, desktop, tablet, grand_total
2 from (select bucket_type, affiliate_id,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total,
7 row_number () over
8 (partition by bucket_type
9 order by sum (impressions) desc) rn
10 from (select decode
11 (lower (bucket_type),
12 'floor_not_met', bucket_type,
13 'api_2.0', bucket_type,
14 'Catch_all') as bucket_type,
15 affiliate_id, device_type, impressions
16 from devicewise_bucket)
17 group by bucket_type, affiliate_id
18 order by bucket_type, affiliate_id, grand_total desc)
19 where rn <= 10
20 order by bucket_type, affiliate_id, grand_total desc
21 /
BUCKET_TYPE Catch_all
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027647 7848 0 722 8570
370027657 6 171 21 198
BUCKET_TYPE floor_not_met
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 4 0 1 5
3 rows selected.
|
|
|
Re: How to display report format data [message #653326 is a reply to message #653325] |
Fri, 01 July 2016 15:24 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I see that, in your other posts, you have used bucket direct_monetized instead of api_2.0. If you want direct_monetized instead, then the following are modified queries.
-- query alone:
SCOTT@orcl_12.1.0.2.0> select bucket_type, affiliate_id, mobile, desktop, tablet, grand_total
2 from (select bucket_type, affiliate_id,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total,
7 row_number () over
8 (partition by bucket_type
9 order by sum (impressions) desc) rn
10 from (select decode
11 (lower (bucket_type),
12 'floor_not_met', bucket_type,
13 'direct_monetized', bucket_type,
14 'Catch_all') as bucket_type,
15 affiliate_id, device_type, impressions
16 from devicewise_bucket)
17 group by bucket_type, affiliate_id
18 order by bucket_type, affiliate_id, grand_total desc)
19 where rn <= 10
20 order by bucket_type, affiliate_id, grand_total desc
21 /
BUCKET_TYPE AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
-------------------- ------------ ---------- ---------- ---------- -----------
Catch_all 370027647 7848 0 722 8570
Catch_all 370027657 6 125 6 137
direct_monetized 370027657 0 46 15 61
floor_not_met 370027657 4 0 1 5
4 rows selected.
-- query with SQL*Plus formatting:
SCOTT@orcl_12.1.0.2.0> break on bucket_type skip page
SCOTT@orcl_12.1.0.2.0> column bucket_type new_value bucketvar noprint
SCOTT@orcl_12.1.0.2.0> ttitle left 'BUCKET_TYPE ' bucketvar
SCOTT@orcl_12.1.0.2.0> column mobile heading "DEVICE_TYPE|MOBILE"
SCOTT@orcl_12.1.0.2.0> select bucket_type, affiliate_id, mobile, desktop, tablet, grand_total
2 from (select bucket_type, affiliate_id,
3 sum (decode (device_type, 'Mobile', impressions, 0)) mobile,
4 sum (decode (device_type, 'Desktop', impressions, 0)) desktop,
5 sum (decode (device_type, 'Tablet', impressions, 0)) tablet,
6 sum (impressions) grand_total,
7 row_number () over
8 (partition by bucket_type
9 order by sum (impressions) desc) rn
10 from (select decode
11 (lower (bucket_type),
12 'floor_not_met', bucket_type,
13 'direct_monetized', bucket_type,
14 'Catch_all') as bucket_type,
15 affiliate_id, device_type, impressions
16 from devicewise_bucket)
17 group by bucket_type, affiliate_id
18 order by bucket_type, affiliate_id, grand_total desc)
19 where rn <= 10
20 order by bucket_type, affiliate_id, grand_total desc
21 /
BUCKET_TYPE Catch_all
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027647 7848 0 722 8570
370027657 6 125 6 137
BUCKET_TYPE direct_monetized
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 0 46 15 61
BUCKET_TYPE floor_not_met
DEVICE_TYPE
AFFILIATE_ID MOBILE DESKTOP TABLET GRAND_TOTAL
------------ ----------- ---------- ---------- -----------
370027657 4 0 1 5
4 rows selected.
|
|
|
Re: How to display report format data [message #653361 is a reply to message #653326] |
Mon, 04 July 2016 02:01 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
Thanks for your Advice.
Now I need to display this type of query format.
bucket_type floor_not_met
device_type Mobile device_type Desktop
Affiliate_name (All) Affiliate_name (All)
AD_SIZE_ID Impressions % of impressions AD_SIZE_ID Impressions % of impressions
320x50 5,408,660 54.68% 728x90 458,955 47.22%
300x250 2,802,742 28.33% 300x250 352,559 36.28%
160x600 985,943 9.97% 160x600 148,908 15.32%
300x600 680,008 6.87% 300x600 8,577 0.88%
120x600 11,803 0.12% 120x600 2,741 0.28%
728x90 2,561 0.03% 320x50 126 0.01%
how to show it?
|
|
|
Re: How to display report format data [message #653410 is a reply to message #653361] |
Mon, 04 July 2016 19:54 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
There aren't any rows in the sample data that you provided with bucket_type floor_not_met and device_type Desktop, so I substituted Tablet for Desktop, so just change that one word. There is also only one ad_size for each, so you don't see the percentages. You should begin to see how things work by now. So, in the future, please try to write the code yourself and, if you get stuck, then post what you tried and what results you got, along with appropriate sample data and desired results.
SCOTT@orcl_12.1.0.2.0> break on bucket_type on device_type skip page
SCOTT@orcl_12.1.0.2.0> column bucket_type new_value bucketvar noprint
SCOTT@orcl_12.1.0.2.0> column device_type new_value devicetypevar noprint
SCOTT@orcl_12.1.0.2.0> ttitle left 'BUCKET_TYPE ' bucketvar skip 'DEVICE_TYPE ' devicetypevar
SCOTT@orcl_12.1.0.2.0> select bucket_type, device_type, ad_size_id, impressions,
2 (impressions /
3 (sum (impressions) over (partition by bucket_type, device_type)))
4 * 100 as "% OF IMPRESSIONS"
5 from devicewise_bucket
6 where bucket_type = 'floor_not_met'
7 and device_type in ('Mobile', 'Tablet')
8 order by bucket_type, device_type, "% OF IMPRESSIONS" desc
9 /
BUCKET_TYPE floor_not_met
DEVICE_TYPE Mobile
AD_SIZE_ID IMPRESSIONS % OF IMPRESSIONS
---------- ----------- ----------------
160x600 4 100
BUCKET_TYPE floor_not_met
DEVICE_TYPE Tablet
AD_SIZE_ID IMPRESSIONS % OF IMPRESSIONS
---------- ----------- ----------------
160x600 1 100
2 rows selected.
|
|
|
Re: How to display report format data [message #653419 is a reply to message #653410] |
Tue, 05 July 2016 01:44 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
Hi All,
I used following query in this query it display correct output.But Grand total is display at the end of each column.But in column heading it display null.How can i show caption to that column.I have used rollup for calculating the grand total.
select device_type,
sum(decode(bucket_type, 'direct_monetized', impressions, 0)) as direct_monetized_imps,
sum(decode(bucket_type, 'error', impressions, 0)) as error_imps,
sum(decode(bucket_type, 'api_2_0', impressions, 0)) as api_2_0_imps,
sum(decode(bucket_type, 'floor_not_met', impressions, 0)) as floor_not_met_imps,
sum(decode(bucket_type, 'glam_exclusion', impressions, 0)) as glam_exclusion_imps,
-- sum(decode(bucket_type, 'missing_rubicon_seq0', impressions, 0)) as missing_rubicon_seq0_imps,
sum(decode(bucket_type, 'no_buy_on_inventory', impressions, 0)) as no_buy_on_inventory_imps,
sum(decode(bucket_type, 'pax_monetized', impressions, 0)) as pax_monetized_imps,
sum(decode(bucket_type, 'sites_blocked_by_partners', impressions, 0)) as sites_blk_part_imps,
sum(decode(bucket_type, 'unknown', impressions, 0)) as Catch_All
from devicewise_bucket
where 1 = 1
and date_id between 20160624 and 20160624
group by rollup(device_type)
order by device_type
|
|
|
Re: How to display report format data [message #653446 is a reply to message #653419] |
Tue, 05 July 2016 15:16 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I changed 20160620 to 20160624 in the sample data to test this. You do not need 1=1 and you do not need between if there is only one date. The grand total is in the bottom row only if the g is lower case and the first letter of the rows above are upper case. Otherwise, you will have to do some different ordering. It uses a decode of the grouping to label the grand total.
SCOTT@orcl_12.1.0.2.0> select decode (grouping(device_type), 1, 'grand total', device_type) device_type,
2 sum(decode(bucket_type, 'direct_monetized', impressions, 0)) as direct_monetized_imps,
3 sum(decode(bucket_type, 'error', impressions, 0)) as error_imps,
4 sum(decode(bucket_type, 'api_2_0', impressions, 0)) as api_2_0_imps,
5 sum(decode(bucket_type, 'floor_not_met', impressions, 0)) as floor_not_met_imps,
6 sum(decode(bucket_type, 'glam_exclusion', impressions, 0)) as glam_exclusion_imps,
7 sum(decode(bucket_type, 'no_buy_on_inventory', impressions, 0)) as no_buy_on_inventory_imps,
8 sum(decode(bucket_type, 'pax_monetized', impressions, 0)) as pax_monetized_imps,
9 sum(decode(bucket_type, 'sites_blocked_by_partners', impressions, 0)) as sites_blk_part_imps,
10 sum(decode(bucket_type, 'unknown', impressions, 0)) as Catch_All
11 from devicewise_bucket
12 where date_id = 20160624
13 group by rollup(device_type)
14 order by device_type
15 /
DEVICE_TYPE DIRECT_MONETIZED_IMPS ERROR_IMPS API_2_0_IMPS FLOOR_NOT_MET_IMPS GLAM_EXCLUSION_IMPS NO_BUY_ON_INVENTORY_IMPS PAX_MONETIZED_IMPS SITES_BLK_PART_IMPS CATCH_ALL
-------------------- --------------------- ---------- ------------ ------------------ ------------------- ------------------------ ------------------ ------------------- ----------
Desktop 46 1 0 0 0 3 33 0 0
Mobile 0 0 0 4 0 0 1 0 7848
Tablet 15 0 0 1 0 0 3 0 722
grand total 61 1 0 5 0 3 37 0 8570
4 rows selected.
|
|
|
|
Re: How to display report format data [message #653460 is a reply to message #653458] |
Wed, 06 July 2016 06:18 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
Hi ,
I am using this query ,it displays output but i have to format the output in the attached format.Plz help me how can i format output.
Thanks in advance.
select ad_size_id,
case when bucket_type like 'unknown' then 'Catch All'
else bucket_type
end as bucket_type,
sum(decode(device_type, 'Mobile', impressions, 0)) mobile,
round(sum(decode(device_type, 'Mobile', impressions, 0)) * 100.00 /
sum(impressions),
2) || '%' mobile_per,
sum(decode(device_type, 'Desktop', impressions, 0)) desktop,
round(sum(decode(device_type, 'Desktop', impressions, 0)) * 100.00 /
sum(impressions),
2) || '%' desktop_per,
sum(decode(device_type, 'Tablet', impressions, 0)) tablet,
round(sum(decode(device_type, 'Tablet', impressions, 0)) * 100.00 /
sum(impressions),
2) || '%' Tablet_per,
sum(impressions) Grand_total
from devicewise_bucket
where 1 = 1 and date_id between 20160625 and 20160628 and bucket_type in ('floor_not_met', 'api_2_0','unknown')
group by ad_size_id,bucket_type
order by bucket_type
[mod-edit: image inserted into message body by bb]
[Updated on: Wed, 06 July 2016 17:38] by Moderator Report message to a moderator
|
|
|
Re: How to display report format data [message #653479 is a reply to message #653460] |
Wed, 06 July 2016 19:42 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I had to create some new sample data to test this properly. In the future, it would help if you would provide insert statements for data to match your desired results.
-- test data:
SCOTT@orcl_12.1.0.2.0> select date_id, bucket_type, device_type, ad_size_id, impressions
2 from devicewise_bucket
3 order by bucket_type, device_type, impressions desc
4 /
DATE_ID BUCKET_TYPE DEVICE_TYPE AD_SIZE_ID IMPRESSIONS
---------- -------------------- -------------------- ---------- -----------
20160625 floor_not_met Desktop 728x90 458955
20160625 floor_not_met Desktop 300x250 352559
20160625 floor_not_met Desktop 160x600 148908
20160625 floor_not_met Desktop 300x600 8577
20160625 floor_not_met Desktop 120x600 2741
20160625 floor_not_met Desktop 320x50 126
20160625 floor_not_met Mobile 320x50 5408660
20160625 floor_not_met Mobile 300x250 2802472
20160625 floor_not_met Mobile 160x600 985943
20160625 floor_not_met Mobile 300x600 680008
20160625 floor_not_met Mobile 120x600 11803
20160625 floor_not_met Mobile 728x90 2561
12 rows selected.
-- query (modified and formatted):
SCOTT@orcl_12.1.0.2.0> break on bucket_type on device_type skip page
SCOTT@orcl_12.1.0.2.0> colum bucket_type new_value bucketvar noprint
SCOTT@orcl_12.1.0.2.0> column device_type new_value devicetypevar noprint
SCOTT@orcl_12.1.0.2.0> ttitle left 'Bucket_type ' bucketvar skip 'Device_type ' devicetypevar skip 'Affiliate_name (All)'
SCOTT@orcl_12.1.0.2.0> column "% OF IMPRESSIONS" format a16
SCOTT@orcl_12.1.0.2.0> select bucket_type, device_type, ad_size_id,
2 sum (impressions) impressions,
3 round (((sum (impressions) / tot_impr) * 100), 2) || '%' "% OF IMPRESSIONS"
4 from (select decode (bucket_type, 'unknown', 'Catch All', bucket_type) bucket_type,
5 device_type, ad_size_id, impressions,
6 sum (impressions) over (partition by bucket_type, device_type) tot_impr
7 from devicewise_bucket
8 where date_id between 20160625 and 20160628
9 and bucket_type in ('floor_not_met', 'api_2_0', 'unknown')
10 and device_type in ('Desktop', 'Mobile', 'Tablet'))
11 group by bucket_type, device_type, ad_size_id, tot_impr
12 order by bucket_type, device_type, impressions desc
13 /
Bucket_type floor_not_met
Device_type Desktop
Affiliate_name (All)
AD_SIZE_ID IMPRESSIONS % OF IMPRESSIONS
---------- ----------- ----------------
728x90 458955 47.22%
300x250 352559 36.28%
160x600 148908 15.32%
300x600 8577 .88%
120x600 2741 .28%
320x50 126 .01%
Bucket_type floor_not_met
Device_type Mobile
Affiliate_name (All)
AD_SIZE_ID IMPRESSIONS % OF IMPRESSIONS
---------- ----------- ----------------
320x50 5408660 54.68%
300x250 2802472 28.33%
160x600 985943 9.97%
300x600 680008 6.87%
120x600 11803 .12%
728x90 2561 .03%
12 rows selected.
|
|
|
Re: How to display report format data [message #654115 is a reply to message #653479] |
Wed, 27 July 2016 03:24 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
Hi All,
Thanks In Advance...
In above query I have to do some modification I have to display row as well column wise sum.
select nvl(device_type,'Total')device_type,
sum(decode(bucket_type, 'direct_monetized', impressions, 0)) as direct_monetized_imps,
sum(decode(bucket_type, 'error', impressions, 0)) as error_imps,
sum(decode(bucket_type, 'api_2_0', impressions, 0)) as api_2_0_imps,
sum(decode(bucket_type, 'floor_not_met', impressions, 0)) as floor_not_met_imps,
sum(decode(bucket_type, 'glam_exclusion', impressions, 0)) as glam_exclusion_imps,
sum(decode(bucket_type, 'no_buy_on_inventory', impressions, 0)) as no_buy_on_inventory_imps,
sum(decode(bucket_type, 'pax_monetized', impressions, 0)) as pax_monetized_imps,
sum(decode(bucket_type, 'sites_blocked_by_partners', impressions, 0)) as sites_blk_part_imps,
sum(decode(bucket_type, 'unknown', impressions, 0)) as Catch_All
from pax_devicewise_bucket
where 1 = 1
and date_id between 20160720 and 20160720
and upper(country_id) in ('US','CA')
and ad_size_id in ('300x250','728x90','160x600','120x600','970x250','970x66','300x600','320x50')
group by rollup(device_type)
order by device_type
How can I show Row wise sum in this query ?
please giveme some idea
|
|
|
|
|
|
|
|
|
|
|
Re: How to display report format data [message #654135 is a reply to message #654134] |
Wed, 27 July 2016 05:36 |
|
ssyr22
Messages: 38 Registered: December 2015 Location: PUNE
|
Member |
|
|
Hi,
I am using this query .You can refer above table structure and data for the same thing.
select nvl(device_type,'Total')device_type,
sum(decode(bucket_type, 'direct_monetized', impressions, 0)) as direct_monetized_imps,
sum(decode(bucket_type, 'error', impressions, 0)) as error_imps,
sum(decode(bucket_type, 'api_2_0', impressions, 0)) as api_2_0_imps,
sum(decode(bucket_type, 'floor_not_met', impressions, 0)) as floor_not_met_imps,
sum(decode(bucket_type, 'glam_exclusion', impressions, 0)) as glam_exclusion_imps,
sum(decode(bucket_type, 'no_buy_on_inventory', impressions, 0)) as no_buy_on_inventory_imps,
sum(decode(bucket_type, 'pax_monetized', impressions, 0)) as pax_monetized_imps,
sum(decode(bucket_type, 'sites_blocked_by_partners', impressions, 0)) as sites_blk_part_imps,
sum(decode(bucket_type, 'unknown', impressions, 0)) as Catch_All
from pax_devicewise_bucket
where 1 = 1
and date_id between 20160720 and 20160720
and upper(country_id) in ('US','CA')
and ad_size_id in ('300x250','728x90','160x600','120x600','970x250','970x66','300x600','320x50')
group by rollup(device_type)
order by device_type;
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 09:50:43 CDT 2024
|