Aggregate functions in PostgreSQL

In PostgreSQL, aggregate functions are used to perform calculations on sets of values and return a single result. Here are some commonly used aggregate functions in PostgreSQL, along with examples:

1. SUM(): Calculates the sum of values in a column.

SELECT SUM(sales_amount) AS total_sales

FROM sales;

2. AVG(): Calculates the average of values in a column.

SELECT AVG(quantity) AS avg_quantity

FROM order_details;

3. COUNT(): Counts the number of rows in a result set or the number of non-null values in a column.

SELECT COUNT(*) AS total_orders

FROM orders;

SELECT COUNT(product_id) AS total_products

FROM products;

4. MIN(): Returns the smallest value in a column.

SELECT MIN(order_date) AS first_order_date

FROM orders;

5. MAX(): Returns the largest value in a column.

SELECT MAX(order_date) AS last_order_date

FROM orders;

6. GROUP BY: Used with aggregate functions to group the result set by one or more columns.

SELECT category_id, AVG(unit_price) AS avg_price

FROM products

GROUP BY category_id;

7. HAVING: Filters groups based on aggregate values.

SELECT customer_id, SUM(order_amount) AS total_orders

FROM orders

GROUP BY customer_id

HAVING SUM(order_amount) > 1000;

These are just a few examples of aggregate functions in PostgreSQL. They allow you to perform various calculations and summaries on your data.