3. Aggregate Functions

Altering Table Structure

To modify the structure of a table, the ALTER statement is used. For example, to add a new column to a table:

ALTER TABLE Humans
ADD Email NVARCHAR(30) NOT NULL DEFAULT ('No Email');

In this example, an Email column is added to the Humans table. For rows where no email exists, the default value No Email is assigned.

Dropping Constraints

To remove a constraint from a table, use the DROP CONSTRAINT clause. For example:

ALTER TABLE Humans
DROP CONSTRAINT ConstraintName;

Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single result. They are commonly used for summarizing data.

COUNT

The COUNT function returns the number of rows in a column.

SELECT COUNT(*) AS TotalCount
FROM Students;

MIN

The MIN function returns the smallest value in a column.

SELECT MIN(Price) AS MinimumPrice
FROM Products;

MAX

The MAX function returns the largest value in a column.

SELECT MAX(Price) AS MaximumPrice
FROM Products;

SUM

The SUM function calculates the total sum of a column’s values.

SELECT SUM(Price) AS TotalPrice
FROM Products;

AVG

The AVG function calculates the average value of a column.

SELECT AVG(Price) AS AveragePrice
FROM Products;

Grouping Data

The GROUP BY statement groups rows that have the same values in specified columns. It is often used with aggregate functions to perform calculations on each group.

SELECT Category, AVG(Price) AS AveragePrice
FROM Products
GROUP BY Category;

Filtering with WHERE and HAVING

• The WHERE clause filters rows before grouping occurs. Aggregate functions cannot be used in WHERE.

• The HAVING clause filters data after grouping and supports aggregate functions.

SELECT Category, AVG(Price) AS AveragePrice
FROM Products
GROUP BY Category
HAVING AVG(Price) > 50;

Sorting Data

The ORDER BY clause is used to sort the final result set. It is always applied after GROUP BY and HAVING.

SELECT Category, SUM(Price) AS TotalPrice
FROM Products
GROUP BY Category
ORDER BY TotalPrice DESC;

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version