Suma SQL con condición. Funciones agregadas de SQL: SUMA, MIN, MAX, AVG, COUNT. Parámetros o argumentos
¿Cómo puedo saber la cantidad de modelos de PC producidos por un proveedor en particular? Cómo determinar el precio medio de ordenadores que tienen el mismo especificaciones? Estas y muchas otras preguntas relacionadas con cierta información estadística se pueden responder utilizando funciones finales (agregadas). El estándar proporciona las siguientes funciones agregadas:
Todas estas funciones devuelven un único valor. Al mismo tiempo, las funciones CUENTA, MÍNIMO Y MÁXIMO aplicable a cualquier tipo de datos, mientras SUMA Y AVG se utilizan sólo para campos numéricos. Diferencia entre función CONTAR(*) Y CONTAR(<имя поля>) es que el segundo no tiene en cuenta los valores NULL al calcular.
Ejemplo. Encuentre el precio mínimo y máximo para computadoras personales:
Ejemplo. Encuentre la cantidad disponible de computadoras producidas por el fabricante A:
Ejemplo. Si estamos interesados en la cantidad de modelos diferentes producidos por el fabricante A, entonces la consulta se puede formular de la siguiente manera (utilizando el hecho de que en la tabla Producto cada modelo se registra una vez):
Ejemplo. Encuentre la cantidad de modelos diferentes disponibles producidos por el fabricante A. La consulta es similar a la anterior, en la que se requería determinar la cantidad total de modelos producidos por el fabricante A. Aquí también necesita encontrar la cantidad de modelos diferentes en la mesa de PC (es decir, los disponibles para la venta).
Para garantizar que solo se utilicen valores únicos al obtener indicadores estadísticos, cuando argumento de funciones agregadas puede ser usado parámetro DISTINTO. Otro parámetro TODOS es el valor predeterminado y supone que se cuentan todos los valores devueltos en la columna. Operador,
Si necesitamos obtener la cantidad de modelos de PC producidos todos fabricante, deberá utilizar Cláusula GRUPO POR, siguiendo sintácticamente DONDE cláusulas.
Cláusula GRUPO POR
Cláusula GRUPO POR Se utiliza para definir grupos de líneas de salida que se pueden aplicar a funciones agregadas (COUNT, MIN, MAX, AVG y SUM). Si falta esta cláusula y se utilizan funciones agregadas, entonces todas las columnas con nombres mencionados en SELECCIONAR, debe incluirse en Funciones agregadas, y estas funciones se aplicarán a todo el conjunto de filas que satisfagan el predicado de la consulta. De lo contrario, todas las columnas de la lista SELECT no incluido en funciones agregadas deben especificarse en la cláusula GROUP BY. Como resultado, todas las filas de la consulta de salida se dividen en grupos caracterizados por las mismas combinaciones de valores en estas columnas. Después de esto, se aplicarán funciones agregadas a cada grupo. Tenga en cuenta que para GROUP BY todos los valores NULL se tratan como iguales, es decir al agrupar por un campo que contiene valores NULL, todas esas filas caerán en un grupo.Si si hay una cláusula GROUP BY, en la cláusula SELECT sin funciones agregadas, entonces la consulta simplemente devolverá una fila de cada grupo. Esta característica, junto con la palabra clave DISTINCT, se puede utilizar para eliminar filas duplicadas en un conjunto de resultados.
Veamos un ejemplo sencillo:
| SELECCIONAR modelo, CONTAR(modelo) COMO Qty_model, AVG(precio) COMO Precio_promedio DESDE PC Agrupar por modelo; |
En esta solicitud, para cada modelo de PC se determina su número y costo promedio. Todas las filas con el mismo valor de modelo forman un grupo, y la salida de SELECT calcula la cantidad de valores y los valores de precio promedio para cada grupo. El resultado de la consulta será la siguiente tabla:
| modelo | Cant_modelo | Precio_promedio |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Si SELECT tuviera una columna de fecha, entonces sería posible calcular estos indicadores para cada fecha específica. Para hacer esto, debe agregar la fecha como una columna de agrupación, y luego las funciones agregadas se calcularían para cada combinación de valores (modelo-fecha).
Hay varios específicos reglas para realizar funciones agregadas:
- Si como resultado de la solicitud no se recibieron filas(o más de una fila para un grupo determinado), entonces no hay datos de origen para calcular ninguna de las funciones agregadas. En este caso, el resultado de las funciones COUNT será cero y el resultado de todas las demás funciones será NULL.
- Argumento función agregada no puede contener por sí mismo funciones agregadas(función de función). Aquellos. en una consulta es imposible, digamos, obtener el máximo de los valores medios.
- El resultado de ejecutar la función CONTAR es entero(ENTERO). Otras funciones agregadas heredan los tipos de datos de los valores que procesan.
- Si la función SUMA produce un resultado mayor que el valor máximo del tipo de datos utilizado, error.
Entonces, si la solicitud no contiene Cláusulas GROUP BY, Eso Funciones agregadas incluido en cláusula SELECCIONAR, se ejecutan en todas las filas de consulta resultantes. Si la solicitud contiene Cláusula GRUPO POR, cada conjunto de filas que tiene los mismos valores de una columna o grupo de columnas especificadas en Cláusula GRUPO POR, forma un grupo, y Funciones agregadas se realizan para cada grupo por separado.
TENER oferta
Si Dónde cláusula define un predicado para filtrar filas, luego TENER oferta aplica después de agrupar para definir un predicado similar que filtre grupos por valores Funciones agregadas. Esta cláusula es necesaria para validar los valores que se obtienen usando función agregada no de filas individuales de la fuente de registro definida en Cláusula DE, y de grupos de tales líneas. Por lo tanto, tal control no puede incluirse en Dónde cláusula.
La función SUMA en lenguaje SQL, a pesar de su simplicidad, se utiliza con bastante frecuencia cuando se trabaja con una base de datos. Con su ayuda conviene obtener unos resultados intermedios o finales sin recurrir a la ayuda de herramientas DBMS auxiliares.
Sintaxis de función
En la mayoría lenguajes SQL la sintaxis de la suma es la misma: solo se utiliza como argumento el nombre del campo o alguna operación aritmética de varios de ellos, sobre el cual se requiere la suma.
En casos excepcionales, es posible transmitir un valor específico como un número o variable, pero estos "esquemas" prácticamente no se utilizan, ya que no tienen mucho valor. A continuación se muestra la sintaxis de la función en SQL:
suma(a): aquí se utiliza algún valor numérico o expresión como parámetro a
Vale la pena señalar que antes del parámetro puede establecer palabras clave, por ejemplo, DISTINCT o ALL, que tomarán solo valores únicos o todos, respectivamente.
Ejemplo de uso de SUM en SQL
Para comprender completamente cómo funciona la función, vale la pena considerar varios ejemplos. En SQL, SUM se puede utilizar como resultado de retorno y como valor intermedio, por ejemplo, para probar una condición.
Para el primer caso, considera la opción cuando necesites devolver el monto de ventas de cada producto, teniendo en cuenta que el número de compras realizadas puede estar en plural. Para obtener el resultado bastará con ejecutar la siguiente consulta:
SELECCIONE Producto, suma (Monto de compra) DE Grupo de ventas por producto;
En respuesta a este comando Habrá una lista única de productos con el monto total de compra para cada uno de ellos.
Para el segundo ejemplo, necesita obtener una lista de productos cuyo monto de ventas excedió un cierto valor, por ejemplo, 100. Puede obtener el resultado de esta tarea de varias maneras, la más óptima de las cuales es ejecutar una solicitud:
SELECCIONE Producto DE (SELECCIONE Producto, suma (Monto de compra) como Monto DE Ventas) DONDE Suma > 100.
SQL - Lección 11. Funciones totales, columnas calculadas y vistas
Las funciones totales también se denominan funciones estadísticas, agregadas o de suma. Estas funciones procesan un conjunto de cadenas para contar y devolver un valor único. Sólo hay cinco de estas funciones:- La función AVG() devuelve el valor promedio de una columna.
- La función COUNT() devuelve el número de filas de una columna.
- La función MAX() devuelve el valor más grande de una columna.
- La función MIN() devuelve el valor más pequeño de la columna.
- SUMA() La función devuelve la suma de los valores de la columna.
SELECCIONE MIN(precio), MAX(precio), AVG(precio) DE los precios;
Ahora queremos saber cuánto nos trajo la mercancía el proveedor "House of Printing" (id=2). Hacer una solicitud así no es tan fácil. Pensemos en cómo componerlo:
1. En primer lugar, en la tabla Suministros (entrantes), seleccione los identificadores (id_incoming) de aquellas entregas que fueron realizadas por el proveedor "Print House" (id=2):
2. Ahora desde la tabla Diario de Suministros (magazine_incoming) es necesario seleccionar las mercancías (id_product) y sus cantidades (quantity), que se realizaron en las entregas que se encuentran en el punto 1. Es decir, la consulta del punto 1 queda anidada:
3. Ahora necesitamos agregar a la tabla resultante los precios de los productos encontrados, que se almacenan en la tabla Precios. Es decir, necesitaremos unir las tablas Revista de Suministros (magazine_incoming) y Precios usando la columna id_producto:
4. La tabla resultante claramente carece de la columna Importe, es decir columna calculada. La capacidad de crear este tipo de columnas se proporciona en MySQL. Para hacer esto, solo necesita especificar en la consulta el nombre de la columna calculada y qué se debe calcular. En nuestro ejemplo, dicha columna se llamará summa y calculará el producto de las columnas de cantidad y precio. El nombre de la nueva columna está separado por la palabra AS:
SELECCIONE magazine_incoming.id_product, magazine_incoming.cantidad, precios.precio, magazine_incoming.cantidad*precios.price COMO resumen DE magazine_incoming, precios DONDE magazine_incoming.id_product= precios.id_product AND id_incoming= (SELECCIONE id_incoming DESDE entrante DONDE id_vendor=2);
5. Genial, todo lo que tenemos que hacer es sumar la columna de suma y finalmente averiguar por cuánto nos trajo la mercancía el proveedor “House of Printing”. La sintaxis para utilizar la función SUMA() es la siguiente:
SELECCIONE SUMA (nombre_columna) DESDE nombre_tabla;
Sabemos el nombre de la columna - summa, pero no tenemos el nombre de la tabla, ya que es el resultado de una consulta. ¿Qué hacer? Para tales casos, MySQL tiene Vistas. Una vista es una consulta de selección a la que se le asigna un nombre único y se puede almacenar en una base de datos para su uso posterior.
La sintaxis para crear una vista es la siguiente:
CREAR VISTA nombre_vista AS solicitud;
Guardemos nuestra solicitud como una vista llamada report_vendor:
CREAR VER report_vendor COMO SELECCIONAR magazine_incoming.id_product, magazine_incoming.cantidad, precios.precio, magazine_incoming.cantidad*precios.precio COMO suma DE magazine_incoming, precios DONDE magazine_incoming.id_product= precios.id_product AND id_incoming= (SELECCIONE id_incoming DESDE entrante DONDE id_vendor=2 );
6. Ahora puedes usar la función final SUMA():
SELECCIONE SUMA(summa) DE report_vendor;
Así logramos el resultado, aunque para ello tuvimos que usar consultas anidadas, uniones, columnas calculadas y vistas. Sí, a veces hay que pensar para obtener un resultado, sin ello no se puede llegar a ninguna parte. Pero tocamos dos temas muy importantes: columnas calculadas y vistas. Hablemos de ellos con más detalle.
Campos calculados (columnas)
Usando un ejemplo, hoy analizamos un campo calculado matemáticamente. Aquí me gustaría agregar que puedes usar no solo la operación de multiplicación (*), sino también la resta (-), la suma (+) y la división (/). La sintaxis es la siguiente:SELECCIONE nombre_columna 1, nombre_columna 2, nombre_columna 1 * nombre_columna 2 AS nombre_columna_calculado FROM nombre_tabla;
El segundo matiz es la palabra clave AS, la usamos para establecer el nombre de la columna calculada. De hecho, esta palabra clave se utiliza para establecer alias para cualquier columna. ¿Por qué es esto necesario? Para reducción y legibilidad del código. Por ejemplo, nuestra vista podría verse así:
CREAR VER report_vendor COMO SELECCIONAR A.id_producto, A.cantidad, B.precio, A.cantidad*B.precio COMO suma DE magazine_incoming COMO A, precios COMO B DONDE A.id_product= B.id_product AND id_incoming= (SELECCIONE id_incoming DE entrante DONDE id_vendor=2);
Estoy de acuerdo en que esto es mucho más breve y claro.
Representación
Ya hemos visto la sintaxis para crear vistas. Una vez creadas las vistas, se pueden utilizar de la misma manera que las tablas. Es decir, ejecutar consultas sobre ellos, filtrar y ordenar datos y combinar algunas vistas con otras. Por un lado, esta es una forma muy cómoda de almacenar los que se utilizan con frecuencia. consultas complejas(como en nuestro ejemplo).Pero recuerde que las vistas no son tablas, es decir, no almacenan datos, solo los recuperan de otras tablas. Por lo tanto, en primer lugar, cuando los datos de las tablas cambian, los resultados de la presentación también cambiarán. Y en segundo lugar, cuando se realiza una solicitud a una vista, se buscan los datos requeridos, es decir, se reduce el rendimiento del DBMS. Por tanto, no debes abusar de ellos.
En eso libro de texto aprenderás a utilizar función SUMA V servidor SQL(Transact-SQL) con sintaxis y ejemplos.
Descripción
En SQL Server (Transact-SQL) función SUMA devuelve el valor total de una expresión.
Sintaxis
La sintaxis de la función SUMA en SQL Server (Transact-SQL) es:
O la sintaxis de la función SUMA al agrupar resultados por una o más columnas es:
Parámetros o argumentos
expresión1, expresión2, ... expresión_n son expresiones que no están incluidas en la función SUM y deben incluirse en la cláusula GROUP BY al final de la declaración SQL.
agregada_expresión es la columna o expresión que se agregará.
tablas: tablas de las que desea obtener registros. Debe haber al menos una tabla listada en la cláusula FROM.
DONDE condiciones - opcional. Estas son las condiciones que deben cumplir los registros seleccionados.
Solicitud
La función SUMA se puede utilizar en las siguientes versiones de SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Ejemplo con un campo
Veamos algunos ejemplos de la función SUM de SQL Server para comprender cómo usar la función SUM en SQL Server (Transact-SQL).
Por ejemplo, puede averiguar la cantidad total de todos los productos cuya cantidad sea superior a 10.
En este ejemplo de la función SUMA, hemos asignado el alias a la expresión SUMA(cantidad) “Cantidad total”. Al devolver un conjunto de resultados, aparecerá "Cantidad total" como nombre del campo.
Ejemplo de uso DISTINTO
Puede utilizar el operador DISTINTO en la función SUMA. Por ejemplo el de abajo declaración SQL devuelve el salario total con valores salariales únicos donde el salario es inferior a $29 000 por año.
Si los dos salarios fueran $24.000 por año, solo uno de esos valores se usaría en la función SUMA.
Ejemplo de uso de la fórmula.
La expresión contenida en la función SUMA no tiene por qué ser un solo campo. También puedes usar la fórmula. Por ejemplo, puedes calcular la comisión total.
Transact-SQL
SELECCIONE SUMA(ventas * 0,03) COMO "Comisión total" DE los pedidos;
SELECCIONE SUMA (ventas * 0,03) COMO "Comisión total" DE pedidos ; |
Ejemplo de uso de GROUP BY
En algunos casos, necesitará utilizar el operador GROUP BY con la función SUMA.
Aprendamos a resumir. No, estos no son los resultados del estudio de SQL, sino los resultados de los valores de las columnas de las tablas de la base de datos. Las funciones agregadas de SQL operan sobre los valores de una columna para producir un único valor resultante. Las funciones agregadas de SQL más utilizadas son SUM, MIN, MAX, AVG y COUNT. Es necesario distinguir entre dos casos de uso de funciones agregadas. En primer lugar, las funciones agregadas se utilizan por sí solas y devuelven un único valor resultante. En segundo lugar, se utilizan funciones agregadas con la cláusula SQL GROUP BY, es decir, agrupar por campos (columnas) para obtener los valores resultantes en cada grupo. Consideremos primero casos de uso de funciones agregadas sin agrupación.
Función SUMA SQL
La función SQL SUM devuelve la suma de los valores en una columna de la tabla de la base de datos. Sólo se puede aplicar a columnas cuyos valores sean números. consultas SQL para que la suma resultante comience así:
SELECCIONE SUMA (NOMBRE_COLUMNA)...
Esta expresión va seguida de FROM (TABLE_NAME) y luego se puede especificar una condición utilizando la cláusula WHERE. Además, el nombre de la columna puede ir precedido de DISTINCT, lo que significa que solo valores únicos. De forma predeterminada, se tienen en cuenta todos los valores (para esto, puede especificar específicamente no DISTINCT, sino TODOS, pero no se requiere la palabra TODOS).
Si desea ejecutar consultas de bases de datos de esta lección en MS SQL Server, pero este DBMS no está instalado en su computadora, puede instalarlo siguiendo las instrucciones en este enlace. .
Primero trabajaremos con la base de datos de la empresa: Empresa1. El script para crear esta base de datos, sus tablas y llenar las tablas con datos se encuentra en el archivo en este enlace .
Ejemplo 1. Existe una base de datos de la empresa con datos sobre sus divisiones y empleados. La tabla Personal también tiene una columna con datos sobre los salarios de los empleados. La selección de la tabla se ve así (para ampliar la imagen, haga clic en ella con el botón izquierdo del mouse):
Para obtener la suma de todos los salarios, utilizamos la siguiente consulta (en MS SQL Server - con la construcción anterior USE empresa1;):
SELECCIONE SUMA (Salario) DEL Personal
Esta consulta devolverá el valor 287664,63.
Y ahora . En los ejercicios ya empezamos a complicar las tareas, acercándolas a las que encontramos en la práctica.
Función mínima de SQL
La función SQL MIN también opera en columnas cuyos valores son números y devuelve el mínimo de todos los valores de la columna. Esta función tiene una sintaxis similar a la de la función SUMA.
Ejemplo 3. La base de datos y la tabla son las mismas que en el ejemplo 1.
Necesito saber el minimo salarios empleados del departamento con el número 42. Para hacer esto, escriba la siguiente consulta (en MS SQL Server - con el constructor USE empresa1 anterior;):
La consulta devolverá el valor 10505,90.
Y otra vez ejercicio para decisión independiente . En este y algunos otros ejercicios, necesitará no solo la tabla Staff, sino también la tabla Org, que contiene datos sobre las divisiones de la empresa:
Ejemplo 4. La tabla Org se agrega a la tabla Staff y contiene datos sobre los departamentos de la empresa. Imprima el número mínimo de años trabajados por un empleado en un departamento ubicado en Boston.
Función SQL MAX
La función SQL MAX funciona de manera similar y tiene una sintaxis similar, que se usa cuando necesita determinar el valor máximo entre todos los valores de una columna.
Ejemplo 5.
Necesitamos averiguar el salario máximo de los empleados en el departamento número 42. Para hacer esto, escriba la siguiente consulta (en MS SQL Server - con el prefijo USE empresa1;):
La consulta devolverá el valor 18352,80.
Es la hora ejercicios para solución independiente.
Ejemplo 6. Volvemos a trabajar con dos tablas: Staff y Org. Muestra el nombre del departamento y el valor máximo de la comisión que recibe un empleado del departamento perteneciente al grupo de departamentos (División) Este. Usar JOIN (unir mesas) .
Función SQL AVG
Lo que se afirma con respecto a la sintaxis de las funciones anteriores descritas también es válido para la función SQL AVG. Esta función devuelve el promedio de todos los valores de una columna.
Ejemplo 7. La base de datos y la tabla son las mismas que en los ejemplos anteriores.
Supongamos que desea conocer la duración promedio de servicio de los empleados en el departamento número 42. Para hacer esto, escriba la siguiente consulta (en MS SQL Server, con el USE de construcción anterior empresa1;):
El resultado será 6,33.
Ejemplo 8. Trabajamos con una mesa: el personal. Muestra el salario promedio de los empleados con 4 a 6 años de experiencia.
Función CUENTA SQL
La función SQL COUNT devuelve el número de registros en una tabla de base de datos. Si especifica SELECT COUNT(COLUMN_NAME) ... en la consulta, el resultado será el número de registros sin tener en cuenta aquellos registros en los que el valor de la columna sea NULL (indefinido). Si utiliza un asterisco como argumento e inicia una consulta SELECT COUNT(*) ..., el resultado será el número de todos los registros (filas) de la tabla.
Ejemplo 9. La base de datos y la tabla son las mismas que en los ejemplos anteriores.
Quiere saber el número de todos los empleados que reciben comisiones. La siguiente consulta devolverá el número de empleados cuyos valores de la columna Comm no son NULL (en MS SQL Server, con el prefijo USE empresa1;):
SELECCIONAR RECUENTO (Comunicación) DEL Personal
El resultado será 11.
Ejemplo 10. La base de datos y la tabla son las mismas que en los ejemplos anteriores.
Si desea conocer el número total de registros en la tabla, utilice una consulta con un asterisco como argumento para la función CONTAR (en MS SQL Server, con la construcción anterior USE empresa1;):
SELECCIONAR RECUENTO (*) DEL Personal
El resultado será 17.
En el proximo ejercicio para la solución independiente necesitarás utilizar una subconsulta.
Ejemplo 11. Trabajamos con una mesa: el personal. Muestra el número de empleados en el departamento de planificación (Plains).
Funciones agregadas con SQL GROUP BY
Ahora veamos el uso de funciones agregadas junto con la declaración SQL GROUP BY. La instrucción SQL GROUP BY se utiliza para agrupar valores de resultados por columnas en una tabla de base de datos. El sitio web tiene una lección dedicada por separado a este operador .
Trabajaremos con la base de datos "Ads Portal 1". El script para crear esta base de datos, su tabla y completar la tabla de datos se encuentra en el archivo en este enlace .
Ejemplo 12. Entonces, existe una base de datos del portal de publicidad. Tiene una tabla de anuncios que contiene datos sobre los anuncios enviados durante la semana. La columna Categoría contiene datos sobre categorías de anuncios grandes (por ejemplo, Bienes raíces) y la columna Partes contiene datos sobre partes más pequeñas incluidas en las categorías (por ejemplo, las partes Apartamentos y Casas de verano son partes de la categoría Bienes raíces). La columna Unidades contiene datos sobre la cantidad de anuncios enviados y la columna Dinero contiene datos sobre la cantidad de dinero recibida por enviar anuncios.
| Categoría | Parte | Unidades | Dinero |
| Transporte | Carros | 110 | 17600 |
| Bienes raíces | Apartamentos | 89 | 18690 |
| Bienes raíces | dacha | 57 | 11970 |
| Transporte | motocicletas | 131 | 20960 |
| Materiales de construcción | tableros | 68 | 7140 |
| Ingenieria Eléctrica | Televisores | 127 | 8255 |
| Ingenieria Eléctrica | Refrigeradores | 137 | 8905 |
| Materiales de construcción | Regímenes | 112 | 11760 |
| Ocio | Libros | 96 | 6240 |
| Bienes raíces | En casa | 47 | 9870 |
| Ocio | Música | 117 | 7605 |
| Ocio | Juegos | 41 | 2665 |
Utilizando la instrucción SQL GROUP BY, encuentre la cantidad de dinero que se gana al publicar anuncios en cada categoría. Escribimos la siguiente consulta (en MS SQL Server - con la construcción anterior USE adportal1;):
SELECCIONE Categoría, SUMA (Dinero) COMO Dinero DEL GRUPO DE ANUNCIOS POR Categoría
Ejemplo 13. La base de datos y la tabla son las mismas que en el ejemplo anterior.
Usando la instrucción SQL GROUP BY, descubra qué parte de cada categoría tenía la mayor cantidad de listados. Escribimos la siguiente consulta (en MS SQL Server - con la construcción anterior USE adportal1;):
SELECCIONE Categoría, Parte, MAX (Unidades) COMO Máximo DEL GRUPO DE ANUNCIOS POR Categoría
El resultado será la siguiente tabla:
Los valores totales e individuales se pueden obtener en una tabla. combinar resultados de consultas utilizando el operador UNION .
Bases de datos relacionales y lenguaje SQL
