SQL Tutorial
Ejemplo de Select con group BY y heaving
The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax
The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.
SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2
Example
Consider the CUSTOMERS table is having the following records −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
If you want to know the total amount of the salary on each customer, then the GROUP BY query would be as follows.
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;
This would produce the following result −
+----------+-------------+ | NAME | SUM(SALARY) | +----------+-------------+ | Chaitali | 6500.00 | | Hardik | 8500.00 | | kaushik | 2000.00 | | Khilan | 1500.00 | | Komal | 4500.00 | | Muffy | 10000.00 | | Ramesh | 2000.00 | +----------+-------------+
Now, let us look at a table where the CUSTOMERS table has the following records with duplicate names −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Ramesh | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | kaushik | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Now again, if you want to know the total amount of salary on each customer, then the GROUP BY query would be as follows −
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;
This would produce the following result −
+---------+-------------+ | NAME | SUM(SALARY) | +---------+-------------+ | Hardik | 8500.00 | | kaushik | 8500.00 | | Komal | 4500.00 | | Muffy | 10000.00 | | Ramesh | 3500.00 | +---------+-------------+
SQL HAVING Clause
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
SQL HAVING Examples
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):
Example
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
Tutorial de SQL: Curso de SQL |
Programador web en Sonora – Consulting and Business Training
- LISTA GENERAL DE FERRETERIA 5600
- productos Papeleria Daguito
- listado.empleados de ejemplo 1050
- excel-11-lista-productos CLASE
herramienta DIAGRAMAS en DIA
MATERIAS DE INGENIERÍA EN SISTEMAS COMPUTACIONALES
Temario: BASES DE DATOS I LISCF
Video Base de Datos #1| Conceptos Básicos
Libros de Apoyo:
- Administracion de Bases de Datos Diseño y desarrollo de aplicaciones Autor Michael V. Mannino 3ra Edicion año 2007 (Libro de Apoyo)
- FUNDAMENTOS-DE-BASES-DE-DATOS-cuarta-edicion-Abraham-Silberschatz-Henry-F-Korth
- fundamentos-de-bases-de-datos-silberschatz-korth-sudarshan
Reglamento:
1. El alumno es responsable de enterarse de su número de faltas y retardos.
2. El alumno debe contar con un mínimo del 80% de asistencia para tener derecho a su calificación Final.
3. El alumno que se sorprenda incurriendo en actos desleales en la elaboración de exámenes, tareas o trabajos, obtendrá cero (0) de calificación en el trabajo, tarea y/o examen.
4. Es responsabilidad del estudiante hablar inmediatamente con el maestro cuando tenga problemas con el material de clase, sus calificaciones, etc. De esta manera evitaremos problemas en el fin del ciclo.
5. Sólo se justifican inasistencias si son autorizadas por la coordinación académica bajo el procedimiento correspondiente.
6. Se tomara asistencia al iniciar la clase.
7. Prohibido utilizar teléfonos celulares y/o aparatos electrónicos dentro del aula.
10. La clase inicia a la hora en punto.
11. No se permiten alimentos ni bebidas dentro del aula.
12. Deberá presentar su Carnet de Pago, expedido por su coordinador administrativo, para la autorización de recepción de trabajos finales y en la aplicación de exámenes.
http://bdalfonso.blogspot.in/2013/06/modelo-err-ejercicios-resueltos.html