Home » SQL & PL/SQL » SQL & PL/SQL » How to display report format data (Oracle 11)
How to display report format data [message #653258] Thu, 30 June 2016 08:05 Go to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Hi All,

Can you please tell me how can i show report format data.

I have attached sheet for sample format as well as data.

Please help me.

Thanks In Advance. Smile

  • Attachment: te_insert.sql
    (Size: 3.74KB, Downloaded 1203 times)
Re: How to display report format data [message #653259 is a reply to message #653258] Thu, 30 June 2016 08:13 Go to previous messageGo to next message
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 #653260 is a reply to message #653259] Thu, 30 June 2016 08:23 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
It should be display in table format.Please check in the attached sheet.
I have to include it in .

Thanks In Advance.
Re: How to display report format data [message #653261 is a reply to message #653260] Thu, 30 June 2016 08:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ssyr22 wrote on Thu, 30 June 2016 06:23
It should be display in table format.


I do not know what above means.

SELECT * FROM DEVICEWISE_BUCKET;
Re: How to display report format data [message #653266 is a reply to message #653260] Thu, 30 June 2016 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What attach sheet?

Re: How to display report format data [message #653270 is a reply to message #653258] Thu, 30 June 2016 11:23 Go to previous messageGo to next message
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 #653272 is a reply to message #653270] Thu, 30 June 2016 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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"?
Re: How to display report format data [message #653273 is a reply to message #653272] Thu, 30 June 2016 13:42 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #653287 is a reply to message #653283] Fri, 01 July 2016 00:45 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Thanks for all for giving me reply.
Re: How to display report format data [message #653288 is a reply to message #653287] Fri, 01 July 2016 00:51 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Hi ,
But I want data to be display for 3 Bucket_Type as well as Device_type. means For ex. Floor_nt_met i have to show top 10 Affiliates with above column data.
This type of information I have to display for 3 Bucket_type.

Plz. Help
Thanks In Adanvce
Re: How to display report format data [message #653290 is a reply to message #653288] Fri, 01 July 2016 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And we want you answer our questions and post what we request.

Plz. Help
Thanks In Adanvce
Re: How to display report format data [message #653291 is a reply to message #653288] Fri, 01 July 2016 01:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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:42
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.




Re: How to display report format data [message #653295 is a reply to message #653294] Fri, 01 July 2016 01:33 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
I am using Oracle11g
Re: How to display report format data [message #653296 is a reply to message #653295] Fri, 01 July 2016 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is not what has been asked and you did not answer ALL other points.

Re: How to display report format data [message #653299 is a reply to message #653291] Fri, 01 July 2016 03:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #653302 is a reply to message #653299] Fri, 01 July 2016 04:04 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Thanks a lot. I thnik it is useful to me. Smile
Re: How to display report format data [message #653304 is a reply to message #653302] Fri, 01 July 2016 04:20 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
If 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?
Re: How to display report format data [message #653314 is a reply to message #653304] Fri, 01 July 2016 07:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What changes I hae to do in this?
write SQL
Re: How to display report format data [message #653325 is a reply to message #653304] Fri, 01 July 2016 15:19 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
ssyr22 wrote on Fri, 01 July 2016 02:20
If 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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #653458 is a reply to message #653446] Wed, 06 July 2016 05:31 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Thanks a Lot...
Re: How to display report format data [message #653460 is a reply to message #653458] Wed, 06 July 2016 06:18 Go to previous messageGo to next message
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
/forum/fa/13191/0/


[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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 #654116 is a reply to message #654115] Wed, 27 July 2016 03:37 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
select x, y, z, x+y+z row_wise_sum
from table
Re: How to display report format data [message #654117 is a reply to message #654116] Wed, 27 July 2016 03:52 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
Hi ,
I want this type of output,

 
   	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	Total
1	Desktop	2628145	62675	2239124	1731942	12739	98723	2890645	378672	871071	10913736
2	Mobile	1074230	47784	115939	4583603	0	501755	2527802	1138828	1604762	11594703
3	Tablet	406169	2165	336097	297848	0	24639	350902	37684	126218	1581722
4	Total	4108544	112624	2691160	6613393	12739	625117	5769349	1555184	2602051	24090161
Re: How to display report format data [message #654119 is a reply to message #654117] Wed, 27 July 2016 04:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And what's wrong with Pablolee's very simple suggestion?
Re: How to display report format data [message #654120 is a reply to message #654119] Wed, 27 July 2016 04:06 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
That 's not working... i have to show row by row ttoal also grand total. As i shown it for column by column

Re: How to display report format data [message #654124 is a reply to message #654120] Wed, 27 July 2016 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're going to have to supply more information than a badly formatted chunk of data.
Which columns are calculated from which other columns?
What is the calculation?
Re: How to display report format data [message #654131 is a reply to message #654124] Wed, 27 July 2016 05:20 Go to previous messageGo to next message
ssyr22
Messages: 38
Registered: December 2015
Location: PUNE
Member
I have attached the format of output.
/forum/fa/13214/0/


[image inserted into message body by bb]
  • Attachment: format.png
    (Size: 26.45KB, Downloaded 1176 times)

[Updated on: Wed, 27 July 2016 13:48] by Moderator

Report message to a moderator

Re: How to display report format data [message #654133 is a reply to message #654131] Wed, 27 July 2016 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
A nicely formatted block of data still doesn't answer the questions I asked. You need to use words.
Re: How to display report format data [message #654134 is a reply to message #654131] Wed, 27 July 2016 05:33 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Presumably, you want the data that you have highlighted in Yellow, yes?

In that case, please explain why my suggestion will not work for you.
Re: How to display report format data [message #654135 is a reply to message #654134] Wed, 27 July 2016 05:36 Go to previous messageGo to next message
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;
Re: How to display report format data [message #654137 is a reply to message #654135] Wed, 27 July 2016 05:39 Go to previous messageGo to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
In what way do you feel that your post answers the question that I asked you?
Previous Topic: unusable index on inserting data into partition table
Next Topic: place set values next to each other
Goto Forum:
  


Current Time: Fri Mar 29 09:29:20 CDT 2024