{"id":347,"date":"2017-09-18T22:30:32","date_gmt":"2017-09-18T22:30:32","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=347"},"modified":"2020-05-19T21:20:58","modified_gmt":"2020-05-19T21:20:58","slug":"uso-de-cube-y-rollup","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=347","title":{"rendered":"Uso de Cube y Rollup para subtotales"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_RollupD.png\" alt=\"\" class=\"wp-image-1163\" width=\"263\" height=\"228\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Agrupamientos incluyendo Subtotales<\/strong> en Select<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Uso de Cube o Rollup<\/strong><\/h2>\n\n\n\n<p>Los operadores Cube y Rollup son extensiones de la cl\u00e1usula Group By (<a href=\"http:\/\/www.manualsqlserver.com\/?p=225\">Ver Agrupamientos<\/a>), permiten calcular&nbsp;subtotales de acuerdo a las agrupaciones especificadas en la consulta.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Importante:<\/strong><br>Para que las opciones funcionen, el nivel de compatibilidad debe ser al menos 100, &nbsp;correspondiente a SQL Server 2008<\/p>\n\n\n\n<p><strong>Usando Northwind<\/strong><br><strong>Cambiar la Compatibilidad de la BD a 100<\/strong><\/p>\n\n\n\n<p>USE master<br>GO<br>ALTER DATABASE Northwind SET COMPATIBILITY_LEVEL = 100<br>GO<br>use Northwind<br>go<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Ejercicios<\/strong><\/h2>\n\n\n\n<p><strong>1.\u00a0 &#8211;Listar los Clientes, la cantidad de \u00f3rdenes por a\u00f1o y el total de las \u00f3rdenes (Valores sumados)<\/strong>.  <strong>Incluir el total por A\u00f1o (use Cube)<\/strong><\/p>\n\n\n\n<p>select C.CompanyName As &#8216;Cliente&#8217;, YEAR(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>Count(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;, Sum(O.Freight) As &#8216;Monto Total&#8217;<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>Group by <strong>Cube<\/strong>(C.CompanyName,YEAR(O.OrderDate))<br>go<\/p>\n\n\n\n<p><strong>Para poder visualizar los resultados, mostramos los clientes cuyo nombre inicia con la letra A<\/strong><\/p>\n\n\n\n<p>select C.CompanyName As &#8216;Cliente&#8217;, YEAR(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>Count(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;, Sum(O.Freight) As &#8216;Monto Total&#8217;<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>where C.CompanyName Like &#8216;A%&#8217;<br>Group by <strong>Cube<\/strong>(C.CompanyName,YEAR(O.OrderDate))<br>go<\/p>\n\n\n\n<p>Los resultados se muestran en la siguiente imagen, note que despu\u00e9s de cada a\u00f1o se incluye un total y al final se incluye un total general.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup01-1024x556.png\" alt=\"\" class=\"wp-image-1160\" width=\"802\" height=\"435\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup01-1024x556.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup01-300x163.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup01-768x417.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup01.png 1130w\" sizes=\"auto, (max-width: 802px) 100vw, 802px\" \/><figcaption>Subtoitales de los clientes cuyo nombre inicia con la letra A.<\/figcaption><\/figure><\/div>\n\n\n\n<p><strong>2. &#8212; Listar los empleados (nombre completo), el A\u00f1o y la cantidad de \u00f3rdenes por a\u00f1o<\/strong><\/p>\n\n\n\n<p>select Empleado = E.LastName+ &#8216; &#8216; + E.FirstName, YEAR(O.OrderDate) As A\u00f1o,<br>Count(O.Orderid) As &#8216;Cantidad de \u00d3rdenes&#8217; from Orders As O<br>join Employees As E<br>on O.EmployeeID = E.EmployeeID<br>Group By Cube(E.LastName+ &#8216; &#8216; + E.FirstName, YEAR(O.OrderDate))<br>go<\/p>\n\n\n\n<p><strong>3. &#8211;Listar la cantidad de \u00f3rdenes por a\u00f1o e incluir el total general al finalizar.<\/strong><\/p>\n\n\n\n<p>select Year(OrderDate) As &#8216;A\u00f1o&#8217;, Count(OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;<br>from orders Group By Year(OrderDate)<br>with rollup<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup02.png\" alt=\"\" class=\"wp-image-1161\" width=\"803\" height=\"282\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup02.png 898w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup02-300x106.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup02-768x270.png 768w\" sizes=\"auto, (max-width: 803px) 100vw, 803px\" \/><\/figure><\/div>\n\n\n\n<p><strong>4. &#8212; Listar los Empleados y la cantidad de \u00f3rdenes y monto total por a\u00f1o, mostrar los totales por Empleado<\/strong><\/p>\n\n\n\n<p>select Empleado = E.LastName+ &#8216; &#8216; + E.FirstName, YEAR(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>Count(O.Orderid) As &#8216;Cantidad de \u00d3rdenes&#8217; from Orders As O<br>join Employees As E<br>on O.EmployeeID = E.EmployeeID<br>Group By E.LastName+ &#8216; &#8216; + E.FirstName, YEAR(O.OrderDate)<br>with rollup<br>go<\/p>\n\n\n\n<p>La imagen muestra parte del resultado con los tres primeros empleados.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup03.png\" alt=\"\" class=\"wp-image-1162\" width=\"811\" height=\"487\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup03.png 886w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup03-300x180.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Cube_Rollup03-768x461.png 768w\" sizes=\"auto, (max-width: 811px) 100vw, 811px\" \/><\/figure><\/div>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Agrupamientos incluyendo Subtotales en Select Uso de Cube o Rollup Los operadores Cube y Rollup son extensiones de la cl\u00e1usula Group By (Ver Agrupamientos), permiten calcular&nbsp;subtotales de acuerdo a las agrupaciones especificadas en la consulta.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=347\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1163,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[96,79,97,51,53],"class_list":["post-347","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","tag-cube","tag-group-by","tag-rollup","tag-select","tag-sqlserver","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/347","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=347"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/347\/revisions"}],"predecessor-version":[{"id":1164,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/347\/revisions\/1164"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1163"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=347"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=347"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=347"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}