Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
| modul:m290:learningunits:lu04:theorie:d_complexselects [2024/09/09 14:56] – vdemir | modul:m290:learningunits:lu04:theorie:d_complexselects [2024/10/17 12:34] (aktuell) – vdemir | ||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| - | ====== | + | ====== |
| SQL (Structured Query Language) provides powerful tools to perform calculations and data analysis on database records. Among these tools, aggregate functions play a crucial role in summarizing data by performing operations on multiple rows and returning a single result. This document focuses on five commonly used SQL aggregate functions: **MIN, MAX, COUNT, SUM, and AVG**, along with practical examples to illustrate their usage. | SQL (Structured Query Language) provides powerful tools to perform calculations and data analysis on database records. Among these tools, aggregate functions play a crucial role in summarizing data by performing operations on multiple rows and returning a single result. This document focuses on five commonly used SQL aggregate functions: **MIN, MAX, COUNT, SUM, and AVG**, along with practical examples to illustrate their usage. | ||
| - | ===== 1. MIN Function ==== | + | ===== 1. MIN Function==== |
| The **MIN** function returns the smallest value in a specified column. This is particularly useful when you need to find the lowest value in a dataset, such as the minimum price of a product or the earliest date in a list of events. | The **MIN** function returns the smallest value in a specified column. This is particularly useful when you need to find the lowest value in a dataset, such as the minimum price of a product or the earliest date in a list of events. | ||
| Zeile 22: | Zeile 22: | ||
| Here, the query retrieves the highest salary from the " | Here, the query retrieves the highest salary from the " | ||
| - | ==== 3. COUNT Function ==== | + | ==== 3. AVG (Average) Function ==== |
| + | The **AVG** function calculates the average value of a numeric column. This is useful for finding the mean value in a dataset, such as the average test score of students or the average price of products in a store. | ||
| + | |||
| + | **Example: | ||
| + | |||
| + | SELECT AVG(price) AS AveragePrice | ||
| + | FROM products; | ||
| + | |||
| + | Here, the query returns the average price of all products in the " | ||
| + | |||
| + | ==== 4. COUNT Function ==== | ||
| The **COUNT** function counts the number of rows that match a specified condition or simply counts all rows in a column. This is useful for determining the size of datasets, such as counting the number of orders placed by a customer or the total number of employees in a department. | The **COUNT** function counts the number of rows that match a specified condition or simply counts all rows in a column. This is useful for determining the size of datasets, such as counting the number of orders placed by a customer or the total number of employees in a department. | ||
| Zeile 33: | Zeile 43: | ||
| This query returns the total number of orders placed by a customer with customer_id 123. | This query returns the total number of orders placed by a customer with customer_id 123. | ||
| - | ==== 4. SUM Function ==== | + | ==== 5. SUM Function ==== |
| The **SUM** function adds up all the values in a specified numeric column. It’s often used for calculating totals, such as the total sales revenue or the sum of all expenses in a financial report. | The **SUM** function adds up all the values in a specified numeric column. It’s often used for calculating totals, such as the total sales revenue or the sum of all expenses in a financial report. | ||
| Zeile 42: | Zeile 52: | ||
| In this example, the query calculates the total revenue by multiplying the quantity of items sold by their price and summing the results. | In this example, the query calculates the total revenue by multiplying the quantity of items sold by their price and summing the results. | ||
| - | |||
| - | ==== 5. AVG (Average) Function ==== | ||
| - | The **AVG** function calculates the average value of a numeric column. This is useful for finding the mean value in a dataset, such as the average test score of students or the average price of products in a store. | ||
| - | |||
| - | **Example: | ||
| - | |||
| - | SELECT AVG(price) AS AveragePrice | ||
| - | FROM products; | ||
| - | |||
| - | Here, the query returns the average price of all products in the " | ||
| ==== 6. Combining Aggregate Functions with GROUP BY ==== | ==== 6. Combining Aggregate Functions with GROUP BY ==== | ||