Multiple rows of data can be calculated using SQL's aggregate functions, which yield a single result.

Common Aggregate Functions

    COUNT(): Returns the number of rows.
    SUM(): Returns the total sum of a numeric column.
    AVG(): Returns the average value of a numeric column.
    MIN(): Returns the minimum value.
    MAX(): Returns the maximum value.

Example Usage of Aggregate Functions

1. Using COUNT()
SELECT COUNT(*) FROM Employees;

2. Using SUM()
SELECT SUM(Salary) FROM Employees;

3. Using AVG()
SELECT AVG(Salary) FROM Employees;

4. Using MIN() and MAX()
SELECT MIN(Salary) FROM Employees;

SELECT MAX(Salary) FROM Employees;

Output
    MIN(Salary): 50,000 (Lowest salary)
    MAX(Salary): 200,000 (Highest salary)

Using GROUP BY with Aggregate Functions
GROUP BY is often used with aggregate functions to group results by one or more columns.
SELECT
 Department, AVG(Salary)
FROM Employees
GROUP BY Department;

Output

Using HAVING with Aggregate Functions

HAVING is used to filter results after aggregation.

SELECT
 Department, COUNT(*)
FROM Employees
GROUP BY Department
 HAVING COUNT(*) > 10;

Output

Advanced Use of Aggregate Functions
Aggregate functions in SQL can be used in advanced ways to solve complex data analysis problems efficiently.

1. Using Aggregate Functions with CASE
SELECT Department,
       SUM(   CASE
                  WHEN Gender = 'Male' THEN
                      1
                  ELSE
                      0
              END
          ) AS Male_Count,
       SUM(   CASE
                  WHEN Gender = 'Female' THEN
                      1
                  ELSE
                      0
              END
          ) AS Female_Count
FROM Employees
GROUP BY Department;

Output

2. Using Aggregate Functions with DISTINCT
SELECT COUNT(DISTINCT Department) AS Total_Departments FROM Employees;

Output. 5 (Total distinct departments)

3. Using Aggregate Functions with PARTITION BY
PARTITION BY allows applying aggregate functions without collapsing rows.
SELECT EmployeeID,
       Name,
       Department,
       Salary,
       AVG(Salary) OVER (PARTITION BY Department) AS Avg_Department_Salary
FROM Employees;

Output

4. Using Aggregate Functions with HAVING for Filtering
SELECT Department,
   COUNT(*) AS Employee_Count
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;

Output. Departments have more than 10 employees.

Advantages of Advanced Aggregate Functions

• Allows detailed data analysis with conditions.
• Enhances reporting and business intelligence capabilities.
• Reduces query complexity using built-in SQL functions.
• Helps in summarizing data.
• Improves query efficiency by reducing result set size.
• Facilitates data analysis and reporting.

These advanced aggregate functions help in efficient query design and deeper data insights.

HostForLIFEASP.NET SQL Server 2022 Hosting

Obtaining the sum of a set of numbers, like the total salary, is frequently required when using SQL searching. For this procedure, SQL has developed special functions called Aggregate Functions or Grouping Functions. When working with numerical and statistical data, aggregate functions are employed for grouping, which enables us to get results following a sequence of simple or complex mathematical computations.

Aggregate functions are predefined functions that carry out the required activities and produce the results when set up during a database query in accordance with our requirements.

Aggregate functions, such as Sum and Count, are characterized by their ability to return a single number after performing particular calculations on the data in a column.

Next, we will examine these functions.

Min Function
This function is used to obtain the minimum value from similar values.
SELECT MIN(grade)
FROM StudentGrade;

In the example above, we use this function to find the lowest score of students in the student grades table.

Max Function
This function is exactly the opposite of the Min function; it is used to find the maximum value among similar values.
SELECT MAX(salary)
FROM Personnel
WHERE Age < 35;

In this example, it finds and displays the highest salary among personnel who are under 35 years old.

Sum Function
This function is used to obtain the sum of numbers.
SELECT SUM(Salary)
FROM Personnel;

In this example, this function is used to sum all the salaries of the personnel in the personnel table.

Count Function
As the name of this function indicates, it is used to obtain the number of items.
SELECT COUNT(Id)
FROM Personnel;

The Count function is used to find the number of personnel.

Avg Function
The AVG function is actually an abbreviation for “average.” Using the AVG function, we can calculate and display the average of the desired values from grouped columns.
SELECT AVG(Salary)
FROM Personnel;

In this example, the AVG function is used to calculate the average salary of the personnel.

HostForLIFEASP.NET SQL Server 2022 Hosting

Easy fix bottlenecks that may be resolved with the appropriate tuning techniques are frequently the cause of SQL Server performance problems. The DELETE statement will be the main topic of this little blog.

Even in simple recovery mode, DELETE statements have the drawback of consuming transaction log space and requiring unnecessary logical reads. Whereas TRUNCATE eliminates every row of a table or partition at the storage for a far quicker and more effective operation, DELETE is a row-based action that produces a lot of logical reads. Although both DELETE and TRUNCATE eliminate data from a table, their behavior varies with regard to rollback capabilities, recovery, logging, and performance.

Performance-wise, the DELETE statement is problematic because it necessitates locking and logging. Because each delete is recorded separately in the transaction log, ROLLBACK is possible. However, when using a WHERE clause to remove specific data within a statement, deleting data with foreign key restrictions, triggering triggers, or conducting rollbacks (outside of a transaction), DELETEs are required. It will be far more effective to use a DROP or TRUNCATE to remove every row if none of these conditions are met. Instead of performing a single action, TRUNCATE dealslocates all of the table's pages, which improves efficiency.

If you can eliminate the need to DELETE the data by using TRUNCATE or DROP instead you can get an immediate performance boost for the query, stored procedure or function. Let’s take a look at a very simple example.

Example
CREATE TABLE ExampleTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Item VARCHAR(100)
);

INSERT INTO ExampleTable (Item)

SELECT TOP 1000000 'SampleItem'

FROM [Production].[TransactionHistoryArchive];  -- Using table to generate rows

SET STATISTICS TIME ON;

DELETE FROM ExampleTable;

SET STATISTICS TIME OFF;

SET STATISTICS TIME ON;

TRUNCATE TABLE ExampleTable;

SET STATISTICS TIME OFF;

RESULTS
DELETE
(89253 rows affected)
SQL Server Execution Times:
CPU time = 328 ms,  elapsed time = 1851 ms.
(89253 rows affected)

TRUNCATE
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 4 ms.
Completion time: 2025-02-27T11:16:28.1156110-05:00

You can easily see the difference.

During code reviews be sure to test the difference in the operations and see if the DELETE is better replaced by something.  If this is not feasible be sure to properly index for the DELETE operation for better efficiency. Remember to keep one key point in mind, because TRUNCATE is not logged, it cannot be rolled back UNLESS it is inside an explicit transaction. So use this power carefully!

HostForLIFEASP.NET SQL Server 2022 Hosting