{"id":265,"date":"2019-08-21T11:13:09","date_gmt":"2019-08-21T09:13:09","guid":{"rendered":"http:\/\/anjo.pt\/wp\/keyword-oracle\/?p=265"},"modified":"2019-08-21T11:14:06","modified_gmt":"2019-08-21T09:14:06","slug":"average-size-of-set-of-rows-from-a-table","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2019\/08\/21\/average-size-of-set-of-rows-from-a-table\/","title":{"rendered":"Average size of set of rows from a table"},"content":{"rendered":"\n<p>Sometimes users have strange requests that I avoid asking the reason.<\/p>\n\n\n\n<p>Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table.<\/p>\n\n\n\n<p>This reminded me from university times, when doing SQL exercises.<\/p>\n\n\n\n<p>With Oracle 11g and listagg function, it is quite easy to fulfill the request:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nset serveroutput on;\n\ndeclare\n  col_list varchar(2500);\n  result number;\nbegin\nselect &#039;select round(&#039;||listagg(&#039;nvl(avg(length(&#039;||column_name||&#039;)),0)&#039;,&#039;+&#039;) within group (order by column_id)||&#039;) \n  from MYAPP.mytable\n  where creationdate &gt; to_date(&#039;&#039;01.01.2018&#039;&#039;,&#039;&#039;dd.mm.yyyy&#039;&#039;) \n    and creationdate &amp;lt; to_date(&#039;&#039;31.12.2018&#039;&#039;,&#039;&#039;dd.mm.yyyy&#039;&#039;)&#039; into col_list \nfrom all_tab_columns where owner=&#039;MYAPP&#039; and table_name=&#039;MYTABLE&#039; ;\nexecute immediate (col_list) into result;\ndbms_output.put_line(result);\nend;\n\/\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"<p>Sometimes users have strange requests that I avoid asking the reason. Today a user asked me how he could find the average size of a set of rows inside a table. Not only for one column and not for the whole table. This reminded me from university times, when doing SQL exercises. With Oracle 11g [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,44],"tags":[],"class_list":{"0":"post-265","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle-11g","7":"category-sql","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/265","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=265"}],"version-history":[{"count":2,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/265\/revisions"}],"predecessor-version":[{"id":267,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/265\/revisions\/267"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=265"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=265"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=265"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}