Sorting Query Results

The ORDER BY clause is utilized to arrange the output of a query. You can specify sorting based on one or multiple columns. By default, the sort order is ascending (ASC), but you can explicitly request descending order (DESC). When handling columns containing NULL values, the standard behavior dictates that in ascending sorts, null values appear at the end of the result set, whereas in descending sorts, they appear at the beginning.

The following example retrieves employee records, sorting them first by department in ascending order and then by salary in descending order:

SELECT *
FROM Employees
ORDER BY DepartmentID ASC, AnnualSalary DESC;

Implementing Aggregate Functions

SQL provides several aggregate functions to perform calculations on data:

• COUNT: Returns the number of rows matching a specific criterion.
• SUM: Calculates the total sum of a numeric column.
• AVG: Computes the average value of a numeric column.
• MAX: Identifies the highest value in a column.
• MIN: Identifies the lowest value in a column.

To ensure accuracy when counting unique items, use the DISTINCT keyword. The example below counts the number of unique orders processed:

SELECT COUNT(DISTINCT OrderID)
FROM OrderDetails;

Grouping Data

The GROUP BY clause categorizes data into summary rows based on the values of one or more columns. This allows aggregate functions to operate on each group individually rather than on the entire dataset.

Rules for usage: When using GROUP BY, any column listed in the SELECT statement must either be part of the GROUP BY clause or be used within an aggregate function.

This query calculates the number of employees in each department:

SELECT Department, COUNT(EmployeeID)
FROM Staff
GROUP BY Department;

Filtering Grouped Results

While the WHERE clause filters individual rows before grouping, the HAVING clause is used to filter groups after the aggregation has occurred. The HAVING clause applies conditions to the aggregated results.

The distinction is crucial: WHERE restricts the input data, whereas HAVING restricts the output groups.

The following example identifies users who have logged more than 10 actions in the system logs:

SELECT UserID
FROM SystemLogs
GROUP BY UserID
HAVING COUNT(ActionID) > 10;