snipt

Ctrl+h for KB shortcuts

SQL

SQL Group By with Rollup for Grand Total

select 
	/* Key bit to save NULL from being the TOTAL field alias */
	IsNull(item.name, 'Total Items') as ItemType, 
    
    count(distinct item.identifier) as Requests FROM tracking INNER JOIN orders
	on tracking.orderID = orders.id INNER JOIN types on tracking.acc=types.acc
	where orders.id=1011
	and tracking.dateordered >= '20130601' and tracking.dateordered <= '20130801'
	
    /* Group by with the rollup option - and because we had the IsNull check above, the rollup total with be named 'Total Items' instead of NULL */
    group by item.name with rollup
    
    /* Optional XML results if required */
	for xml path('entity'), root('items')
https://snipt.net/embed/d3410a9cc46bf3c2625b328d8347097f/
/raw/d3410a9cc46bf3c2625b328d8347097f/
d3410a9cc46bf3c2625b328d8347097f
sql
SQL
14
2019-06-18T10:52:31
True
False
False
Jul 26, 2013 at 06:48 AM
/api/public/snipt/69704/
sql-group-by-with-rollup-for-grand-total
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">select</span> </span><span id="L-2"><a name="L-2"></a> <span class="cm">/* Key bit to save NULL from being the TOTAL field alias */</span> </span><span id="L-3"><a name="L-3"></a> <span class="k">IsNull</span><span class="p">(</span><span class="n">item</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="s1">&#39;Total Items&#39;</span><span class="p">)</span> <span class="k">as</span> <span class="n">ItemType</span><span class="p">,</span> </span><span id="L-4"><a name="L-4"></a> </span><span id="L-5"><a name="L-5"></a> <span class="k">count</span><span class="p">(</span><span class="k">distinct</span> <span class="n">item</span><span class="p">.</span><span class="n">identifier</span><span class="p">)</span> <span class="k">as</span> <span class="n">Requests</span> <span class="k">FROM</span> <span class="n">tracking</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">orders</span> </span><span id="L-6"><a name="L-6"></a> <span class="k">on</span> <span class="n">tracking</span><span class="p">.</span><span class="n">orderID</span> <span class="o">=</span> <span class="n">orders</span><span class="p">.</span><span class="n">id</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">types</span> <span class="k">on</span> <span class="n">tracking</span><span class="p">.</span><span class="n">acc</span><span class="o">=</span><span class="n">types</span><span class="p">.</span><span class="n">acc</span> </span><span id="L-7"><a name="L-7"></a> <span class="k">where</span> <span class="n">orders</span><span class="p">.</span><span class="n">id</span><span class="o">=</span><span class="mi">1011</span> </span><span id="L-8"><a name="L-8"></a> <span class="k">and</span> <span class="n">tracking</span><span class="p">.</span><span class="n">dateordered</span> <span class="o">&gt;=</span> <span class="s1">&#39;20130601&#39;</span> <span class="k">and</span> <span class="n">tracking</span><span class="p">.</span><span class="n">dateordered</span> <span class="o">&lt;=</span> <span class="s1">&#39;20130801&#39;</span> </span><span id="L-9"><a name="L-9"></a> </span><span id="L-10"><a name="L-10"></a> <span class="cm">/* Group by with the rollup option - and because we had the IsNull check above, the rollup total with be named &#39;Total Items&#39; instead of NULL */</span> </span><span id="L-11"><a name="L-11"></a> <span class="k">group</span> <span class="k">by</span> <span class="n">item</span><span class="p">.</span><span class="n">name</span> <span class="k">with</span> <span class="k">rollup</span> </span><span id="L-12"><a name="L-12"></a> </span><span id="L-13"><a name="L-13"></a> <span class="cm">/* Optional XML results if required */</span> </span><span id="L-14"><a name="L-14"></a> <span class="k">for</span> <span class="n">xml</span> <span class="n">path</span><span class="p">(</span><span class="s1">&#39;entity&#39;</span><span class="p">),</span> <span class="n">root</span><span class="p">(</span><span class="s1">&#39;items&#39;</span><span class="p">)</span> </span></pre></div> </td></tr></table>
group, rollup, sql