Databases
StackOverflow2013
Postsdbo
POhow to multiple Aggregate from XMLTYPE field within 1 sql?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to multiple Aggregate from XMLTYPE field within 1 sql?
    text
    copied!<p>Belows are the xml data stored in a field call xmldoc with data type XMLTYPE and the pattern are stored in each record.</p> <p>The first row(record) of the xmldoc field should be:</p> <pre><code>&lt;cdata&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;1180&lt;/data&gt; &lt;/r&gt; &lt;/cdata&gt; </code></pre> <p>and the second row of the xmldoc field should be:</p> <pre><code>&lt;cdata&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;1280&lt;/data&gt; &lt;/r&gt; &lt;/cdata&gt; </code></pre> <p>and the belowed xml datas are also modified into the upper example (each data stored in a row record in xmldoc field and embedded a 'cdata' root element.)</p> <pre><code>&lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;1380&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;2180&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;2280&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2009&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;2380&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;1181&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;1281&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Jan&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;1381&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;1&lt;/day&gt; &lt;data&gt;2181&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;2&lt;/day&gt; &lt;data&gt;2281&lt;/data&gt; &lt;/r&gt; &lt;r&gt; &lt;year&gt;2010&lt;/year&gt; &lt;month&gt;Feb&lt;/month&gt; &lt;day&gt;3&lt;/day&gt; &lt;data&gt;2381&lt;/data&gt; &lt;/r&gt; </code></pre> <p>now, i use this sql:</p> <pre><code>SELECT X.year, x.month, sum(x.data) as sumMonth FROM xmltest, XMLTABLE ('$d/cdata/r' passing xmldoc as "d" COLUMNS year integer path 'year', month varchar(3) path 'month', day varchar(2) path 'day', data float path 'data' ) AS X group by x.year, x.month </code></pre> <p>order by x.year, x.month</p> <p>i can get the sum of 'data' by each month in each year from this sql and the question i would like to know is how to display the sum of each year after the column sumMonth and just modify this sql but <strong>not use two or more sql</strong> to get the aggregation of month and year in same time.</p> <p>the output maybe like this:</p> <pre><code>year month sumMonth sumYear 2009 Jan 3840 10680 2009 Feb 6840 10680 2010 Jan 3843 10686 2010 Feb 6843 10686 </code></pre> <p>thanks everyone gave me the answer :)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload