Aggregate functions in DB2

In IBM DB2, you can use various aggregate functions to perform calculations on groups of rows to return a single result. Here are some commonly used aggregate functions in DB2 with examples:

1. COUNT: Counts the number of rows in a group, including rows with NULL values (unless otherwise specified).

    SELECT COUNT(column_name) FROM table_name;

2. SUM: Calculates the sum of values in a group.

    SELECT SUM(column_name) FROM table_name;

3. AVG: Calculates the average of values in a group.

    SELECT AVG(column_name) FROM table_name;

4. MIN: Returns the minimum value in a group.

    SELECT MIN(column_name) FROM table_name;

5. MAX: Returns the maximum value in a group.

    SELECT MAX(column_name) FROM table_name;

6. GROUP BY: Groups rows that have the same values into summary rows.

    SELECT column_name, SUM(column_name)

    FROM table_name

    GROUP BY column_name;

7. HAVING: Specifies a search condition for a group or an aggregate.

    SELECT column_name, SUM(column_name)

    FROM table_name

    GROUP BY column_name

    HAVING SUM(column_name) > 1000;

8. DISTINCT: Returns unique values from a column.

    SELECT DISTINCT column_name FROM table_name;

These aggregate functions are commonly used in combination with the SELECT statement and can be very powerful for summarizing and analyzing data in DB2 databases.