{"id":225,"date":"2017-09-18T23:04:00","date_gmt":"2017-09-18T23:04:00","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=225"},"modified":"2020-05-19T04:22:58","modified_gmt":"2020-05-19T04:22:58","slug":"agrupamientos-group-by-filtros-having","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=225","title":{"rendered":"Agrupamientos SQL Server &#8211; Group By &#8211; Filtros Having"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"245\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_AgrupamientosD.png\" alt=\"\" class=\"wp-image-1098\"\/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Agrupamientos en un Select &nbsp;&#8211; Usando Group by<\/strong><\/h1>\n\n\n\n<p>Los agrupamientos en SQL Server se utilizan cuando en un listado existen funciones de agregado en algunos campos con campos que no las tienen. (<a href=\"https:\/\/manualsqlserver.com\/?p=207\">Ver Funciones de agregado<\/a>)<\/p>\n\n\n\n<p>Generalmente los agrupamientos se utilizan para mostrar resultados estad\u00edsticos con datos de varias tablas (<a href=\"https:\/\/manualsqlserver.com\/?p=215\">Ver Listado con Joins<\/a>). Tambi\u00e9n en muchas ocasiones se puede hacer el listado usando Subconsultas (<a href=\"https:\/\/manualsqlserver.com\/?p=219\">Ver Subconsultas<\/a>). Puede usar el Plan de ejecuci\u00f3n para analizar cual de las opciones es la mejor seg\u00fan el dise\u00f1o de las tablas utilizadas.<br><script>&lt;br \/>      (adsbygoogle = window.adsbygoogle || []).push({});&lt;br \/> <\/script><\/p>\n\n\n\n<!--more-->\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\">Ejercicios<\/h2>\n\n\n\n<p><strong>Usando Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<p><strong>1. &#8212; Listar las categor\u00edas y la cantidad de productos que tiene. U<\/strong><strong>sando Subconsultas. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=219\">Ver Subconsultas<\/a>)<\/strong><\/p>\n\n\n\n<p><strong>Primero, para saber cuantos productos hay de una categor\u00eda, por ejemplo para la Categor\u00eda 1<\/strong><br>select Count(P.CategoryID) As &#8216;Cantidad de Productos&#8217; from Products As P where P.CategoryID = 1<br>go<\/p>\n\n\n\n<p><strong>La instrucci\u00f3n anterior se incluye en el listado y cambiar el n\u00famero de la categor\u00eda que se utiliz\u00f3 como ejemplo por el campo de Categor\u00edas.<\/strong><br>Select C.CategoryID As &#8216;C\u00f3digo&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;, &nbsp;(select Count(P.CategoryID) from Products As P<br>where P.CategoryID = C.CategoryID) As &#8216;Cantidad de Productos&#8217;&nbsp;from Categories As C<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_Agrupamientos05.png\" alt=\"\" class=\"wp-image-1096\" width=\"721\" height=\"372\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos05.png 491w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos05-300x155.png 300w\" sizes=\"auto, (max-width: 721px) 100vw, 721px\" \/><\/figure><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Usando Agrupamientos<\/strong><\/p>\n\n\n\n<p>Select C.CategoryID As &#8216;C\u00f3digo&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;, \u00a0Count(P.CategoryID) As &#8216;Cantidad de Productos&#8217;<br>from Categories As C<br>join Products As P on C.CategoryID = P.CategoryID<br><strong>group by C.CategoryID, C.CategoryName<\/strong><br>go<br><strong>&#8212; Note que los campos que no tienen funci\u00f3n de agregado debe incluirse en la cl\u00e1usula Group By.<\/strong><\/p>\n\n\n\n<p><strong>2.&#8211; Listado de los empleados y la cantidad de \u00f3rdenes generadas y el monto total de las ordenes<\/strong><\/p>\n\n\n\n<p>select Empleado = E.LastName + SPACE(1) + E.FirstName,<br>Count(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;,<br>Sum(O.Freight) As &#8216;Monto total&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>Group by E.LastName + SPACE(1) + E.FirstName<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_Agrupamientos06.png\" alt=\"\" class=\"wp-image-1097\" width=\"744\" height=\"383\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos06.png 560w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos06-300x154.png 300w\" sizes=\"auto, (max-width: 744px) 100vw, 744px\" \/><\/figure><\/div>\n\n\n\n<p><strong>3. &#8212; Ordenar la orden anterior por cantidad de \u00f3rdenes<\/strong><\/p>\n\n\n\n<p>select Empleado = E.LastName + SPACE(1) + E.FirstName,<br>Count(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;,<br>Sum(O.Freight) As &#8216;Monto total&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>Group by E.LastName + SPACE(1) + E.FirstName<br>order by [Cantidad de \u00d3rdenes] desc<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_Agrupamientos01.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos01.png\" alt=\"\" class=\"wp-image-1092\" width=\"797\" height=\"390\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos01.png 627w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos01-300x147.png 300w\" sizes=\"auto, (max-width: 797px) 100vw, 797px\" \/><\/a><\/figure><\/div>\n\n\n\n<p><strong>4. &#8212; Para listar los que tienen m\u00e1s de 100 \u00f3rdenes \u00fanicamente. Se utiliza en filtrado con Having<\/strong><\/p>\n\n\n\n<p>select Empleado = E.LastName + SPACE(1) + E.FirstName,<br>Count(O.OrderID) As &#8216;Cantidad de \u00d3rdenes&#8217;,<br>Sum(O.Freight) As &#8216;Monto total&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>Group by E.LastName + SPACE(1) + E.FirstName<br><strong>having Count(O.OrderID) > 100<\/strong><br>order by [Cantidad de \u00d3rdenes] desc<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_Agrupamientos02.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos02.png\" alt=\"\" class=\"wp-image-1093\" width=\"737\" height=\"226\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos02.png 627w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos02-300x92.png 300w\" sizes=\"auto, (max-width: 737px) 100vw, 737px\" \/><\/a><\/figure><\/div>\n\n\n\n<p><strong>5. &#8212; Los productos de categor\u00edas 3,5,6 y 8 y la cantidad vendida de cada uno, incluir el c\u00f3digo y descripci\u00f3n de la categor\u00eda<\/strong><\/p>\n\n\n\n<p>El diagrama muestra las relaciones de las tablas a utilizar.<\/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_Agrupamientos03.png\" alt=\"\" class=\"wp-image-1094\" width=\"834\" height=\"623\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos03.png 707w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos03-300x224.png 300w\" sizes=\"auto, (max-width: 834px) 100vw, 834px\" \/><\/figure><\/div>\n\n\n\n<p><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_Agrupamientos03.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-229\" src=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_Agrupamientos03-300x224.png\" alt=\"\" width=\"408\" height=\"305\"><\/a><br>select P.ProductID As &#8216;C\u00f3d. Producto&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;, SUM(OD.Quantity) As &#8216;Cantidad Vendida&#8217;, C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;,<br>C.CategoryName As &#8216;Nombre Categor\u00eda&#8217;<br>from Products As P<br>join [Order Details] As OD on P.ProductID = OD.ProductID<br>join Categories As C on P.CategoryID = C.CategoryID<br>where C.CategoryID in (3,5,6,8)<br>Group by P.ProductID, P.ProductName, C.CategoryID, C.CategoryName<br>order by C.CategoryID asc, [Cantidad Vendida] desc<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\n<p><strong>6. &#8212; Si se desea filtrar por la cantidad vendida, por ejemplo, productos con mas de 600 unidades vendidas.<\/strong><\/p>\n\n\n\n<p>select P.ProductID As &#8216;C\u00f3d. Producto&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>SUM(OD.Quantity) As &#8216;Cantidad Vendida&#8217;, C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;,<br>C.CategoryName As &#8216;Nombre Categor\u00eda&#8217;<br>from Products As P<br>join [Order Details] As OD on P.ProductID = OD.ProductID<br>join Categories As C on P.CategoryID = C.CategoryID<br>where C.CategoryID in (3,5,6,8)<br>Group by P.ProductID, P.ProductName, C.CategoryID, C.CategoryName<br>Having SUM(OD.Quantity) &gt; 600<br>order by C.CategoryID asc, [Cantidad Vendida] desc<br>go<\/p>\n\n\n\n<p>Note la figura siguiente donde se destaca los bloques donde est\u00e1n las categor\u00edas y los valores menores en la cantidad que cumplen con el filtro de la cl\u00e1usula Having.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_Agrupamientos04.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos04-1024x620.png\" alt=\"\" class=\"wp-image-1095\" width=\"834\" height=\"504\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos04-1024x620.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos04-300x182.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos04-768x465.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_Agrupamientos04.png 1044w\" sizes=\"auto, (max-width: 834px) 100vw, 834px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Agrupamientos en un Select &nbsp;&#8211; Usando Group by Los agrupamientos en SQL Server se utilizan cuando en un listado existen funciones de agregado en algunos campos con campos que no las tienen. (Ver Funciones de agregado) Generalmente los agrupamientos se utilizan para mostrar resultados estad\u00edsticos con datos de varias tablas (Ver Listado con Joins). Tambi\u00e9n &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=225\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1098,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[79,80,40,53],"class_list":["post-225","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","tag-group-by","tag-having","tag-join","tag-sqlserver","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/225","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=225"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/225\/revisions"}],"predecessor-version":[{"id":1099,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/225\/revisions\/1099"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1098"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=225"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}