{"id":2671,"date":"2022-08-11T20:08:42","date_gmt":"2022-08-11T20:08:42","guid":{"rendered":"https:\/\/manualsqlserver.com\/?p=2671"},"modified":"2022-08-11T20:08:43","modified_gmt":"2022-08-11T20:08:43","slug":"funcion-count_big-de-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2671","title":{"rendered":"Funci\u00f3n count_big de SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big__D.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big__D.png\" alt=\"\" class=\"wp-image-2665\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Funci\u00f3n Count_Big en SQL Server<\/h2>\n\n\n\n<p>La funci\u00f3n Count_big retorna la cantidad de elementos encontrados en un agrupamiento. La funci\u00f3n Count_big es similar a la funci\u00f3n de agregado Count. Count_Big retorna un tipo de dato bigint, mientras que Count retorna un tipo de dato int.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Sintaxis<\/strong><br>COUNT_BIG ( { [ [ ALL | DISTINCT ] expresi\u00f3n ] | * } )<\/p>\n\n\n\n<p><strong>Donde<\/strong><br><strong>ALL<\/strong><br>La funci\u00f3n agregada se aplica a todos los valores. ALL es el valor predeterminado.<br><strong>DISTINCT<\/strong><br>Indica que COUNT_BIG devuelva el n\u00famero de valores no nulos \u00fanicos.<br><strong>expresi\u00f3n<\/strong><br>Una expresi\u00f3n de cualquier tipo. COUNT_BIG no admite funciones agregadas ni subconsultas<\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Para m\u00e1s informaci\u00f3n ver:<br><\/strong><a href=\"https:\/\/manualsqlserver.com\/?p=207\" target=\"_blank\" rel=\"noreferrer noopener\">Funciones de agregado<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=310\" target=\"_blank\" rel=\"noreferrer noopener\">Vistas en SQL Server<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=158\" target=\"_blank\" rel=\"noreferrer noopener\">\u00cdndices en SQL Server<\/a><\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n\n\n\n<p><strong>Usando la base de datos Northwind<br><\/strong>use Northwind<br>go<\/p>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Listar los empleados y la cantidad de \u00f3rdenes de cada uno.<\/strong><br>select<br>E.LastName,<br>E.FirstName,<br>E.Address,<br>COUNT_BIG(O.OrderId) As 'Cantidad de \u00f3rdenes'<br>from dbo.Employees As E<br>join dbo.Orders As O on E.EmployeeID = O.EmployeeID<br>group by E.LastName, E.FirstName, E.Address<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_01.png\"><img loading=\"lazy\" decoding=\"async\" width=\"703\" height=\"315\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_01.png\" alt=\"\" class=\"wp-image-2666\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_01.png 703w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_01-300x134.png 300w\" sizes=\"auto, (max-width: 703px) 100vw, 703px\" \/><\/a><figcaption>Listado de empleados y la cantidad de \u00f3rdenes<\/figcaption><\/figure>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Listar los productos, en cuantas \u00f3rdenes se vendieron y la cantidad de unidades vendidas<br><\/strong>select<br>P.ProductID,<br>P.ProductName,<br>P.QuantityPerUnit,<br>COUNT_BIG(D.Quantity) As 'Cantidad de \u00d3rdenes',<br>sum(D.Quantity) As 'Cantidad de Unidades'<br>from dbo.Products As P<br>join dbo.[Order Details] As D on P.ProductID = D.ProductID<br>group by P.ProductID, P.ProductName,P.QuantityPerUnit<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_02.png\"><img loading=\"lazy\" decoding=\"async\" width=\"847\" height=\"660\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_02.png\" alt=\"\" class=\"wp-image-2667\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_02.png 847w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_02-300x234.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_02-768x598.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_02-795x619.png 795w\" sizes=\"auto, (max-width: 847px) 100vw, 847px\" \/><\/a><figcaption>Productos, cantidad de \u00f3rdenes y cantidad de unidades vendidas.<\/figcaption><\/figure>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Crear una vista con los proveedores, la cantidad de productos y la cantidad de unidades en stock, luego crear un \u00edndice para la vista.<\/strong><\/p>\n\n\n\n<p><strong>El campo UnitsInStock no debe soportar valores null<\/strong><br>Alter table Products alter column UnitsInStock smallint not null<br>go<\/p>\n\n\n\n<p>Create or alter view vistaProveedoresProductosStock<br>with schemaBinding<br>As<br>Select<br>S.SupplierID As 'C\u00f3d. Proveedor',<br>S.CompanyName As 'Proveedor',<br>COUNT_BIG(*) As 'Cantidad Productos' ,<br>sum(P.UnitsInStock) As 'Unidades'<br>from dbo.Products As P<br>join dbo.Suppliers As S on P.SupplierID = S.SupplierID<br>group by S.SupplierID, S.CompanyName<br>go<\/p>\n\n\n\n<p><strong>Para visualizar los registros de la vista<\/strong><br>select * from vistaProveedoresProductosStock<br>go<br><strong>El resultado se muestra en la siguiente imagen.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_03.png\"><img loading=\"lazy\" decoding=\"async\" width=\"754\" height=\"658\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_03.png\" alt=\"\" class=\"wp-image-2668\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_03.png 754w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_03-300x262.png 300w\" sizes=\"auto, (max-width: 754px) 100vw, 754px\" \/><\/a><figcaption>Proveedores y productos en stock.<\/figcaption><\/figure>\n\n\n\n<p>Crear el \u00edndice<br>drop index if exists vistaProveedoresIDXa on vistaProveedoresProductosStock<br>go<br>Create unique clustered index vistaProveedoresIDXa<br>on vistaProveedoresProductosStock([C\u00f3d. Proveedor], [Proveedor])<br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p>Listado de los clientes, sus \u00f3rdenes y el monto total de las mismas.<br>select<br>C.CompanyName As 'Cliente',<br>COUNT_BIG(distinct D.OrderID) As 'Cantidad de \u00f3rdenes',<br>sum((D.Quantity * D.UnitPrice) *(1-D.Discount)) As 'Monto total'<br>from dbo.Customers As C<br>join dbo.Orders As O on C.CustomerID = O.CustomerID<br>join dbo.[Order Details] As D on O.OrderID = D.OrderID<br>group by C.CompanyName<br>go<br><strong>El resultado se muestre en la siguiente imagen.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_04.png\"><img loading=\"lazy\" decoding=\"async\" width=\"669\" height=\"662\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_04.png\" alt=\"\" class=\"wp-image-2669\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_04.png 669w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_04-300x297.png 300w\" sizes=\"auto, (max-width: 669px) 100vw, 669px\" \/><\/a><figcaption>Clientes, cantidad de \u00f3rdenes y total comprado.<\/figcaption><\/figure>\n\n\n\n<p>Para comprobar el resultado, calcular el total de las \u00f3rdenes del clientes ALFKI, el valor es 4272.99<br>Total de las \u00f3rdenes de Alfreds Futerkiste<br>select<br>sum((D.Quantity * D.UnitPrice) *(1-D.Discount))<br>from dbo.[Order Details] As D<br>where D.OrderID in<br>(select O.OrderID from dbo.Orders As O<br>where O.CustomerID = 'ALFKI')<br>go<br><strong>El resultado se muestre en la siguiente imagen.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_05.png\"><img loading=\"lazy\" decoding=\"async\" width=\"334\" height=\"126\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_05.png\" alt=\"\" class=\"wp-image-2670\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_05.png 334w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_UsandoCount_Big_05-300x113.png 300w\" sizes=\"auto, (max-width: 334px) 100vw, 334px\" \/><\/a><\/figure>\n\n\n\n<p><strong>Note el mismo resultado de la imagen anterior.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Funci\u00f3n Count_Big en SQL Server La funci\u00f3n Count_big retorna la cantidad de elementos encontrados en un agrupamiento. La funci\u00f3n Count_big es similar a la funci\u00f3n de agregado Count. Count_Big retorna un tipo de dato bigint, mientras que Count retorna un tipo de dato int.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2671\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2665,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[288,286],"class_list":["post-2671","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-funcionessql","tag-count_big","tag-funciones-sql-server","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2671","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=2671"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2671\/revisions"}],"predecessor-version":[{"id":2672,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2671\/revisions\/2672"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2665"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}