PostgreSQL

In PostgreSQL, Data Query Language (DQL) is used to query and retrieve data from the database. The most commonly used DQL statements are SELECT queries, and PostgreSQL provides several aggregate functions—such as COUNT, SUM, AVG, MIN, and MAX—to help retrieve summarized data. These functions are incredibly useful in real-life scenarios like calculating totals, averages, and determining the highest or lowest values from a dataset. Below is a beginner-friendly explanation of each function with sample SQL queries based on typical banking scenarios, such as customers, accounts, and transactions.

1. COUNT Function

The COUNT() function is used to count the number of rows in a table, which is particularly helpful when you need to know how many records exist for a certain condition.

-- Count how many customers are in the database
SELECT COUNT(*) AS total_customers
FROM customers;

• COUNT(*) counts all rows.
• You can also specify a column to count non-null values.

2. SUM Function

The SUM() function returns the total sum of a numeric column. This is typically used for calculating financial sums, such as the total value of transactions.

-- Calculate the total amount of all transactions
SELECT SUM(amount) AS total_transactions
FROM transactions;

• SUM() only works on numeric columns.

3. AVG Function

The AVG() function returns the average of a numeric column, which is useful for calculating averages such as the average transaction amount.

-- Calculate the average transaction amount
SELECT AVG(amount) AS average_transaction
FROM transactions;

• AVG() is especially helpful when you want to determine the mean value.

4. MIN Function

The MIN() function retrieves the smallest value from a column, such as the minimum balance in customer accounts.

-- Find the minimum balance in all customer accounts
SELECT MIN(balance) AS minimum_balance
FROM accounts;

• This function helps in retrieving the lowest value from a column.

5. MAX Function

The MAX() function retrieves the largest value from a column, such as the highest transaction amount.

-- Find the highest transaction amount
SELECT MAX(amount) AS maximum_transaction
FROM transactions;

• MAX() is useful for finding the largest or most recent value, like the latest transaction date.

Example Data

If required, the tables in a typical banking database might look like this:

• customers (id, name, email)
• accounts (id, customer_id, balance)
• transactions (id, account_id, amount, transaction_date)

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT COUNT(*)
FROM org_employee;

SELECT COUNT(DISTINCT department)
FROM org_employee;

SELECT SUM(salary)
FROM org_employee;

SELECT AVG(salary)
FROM org_employee;

SELECT MIN(salary)
FROM org_employee;

SELECT MAX(salary)
FROM org_employee;

SELECT a.*
FROM org_employee a
INNER JOIN (SELECT MIN(salary) AS min_salary FROM org_employee) b
ON a.salary = b.min_salary;

SELECT department,
       SUM(salary) AS total_salary
FROM org_employee
GROUP BY department
ORDER BY total_salary DESC;

SELECT department,
       COUNT(*) AS employee_count
FROM org_employee
GROUP BY department;

SELECT department,
       MAX(salary) AS highest_salary
FROM org_employee
GROUP BY department;

SELECT department,
       MIN(salary) AS lowest_salary
FROM org_employee
GROUP BY department;

Example 1:

Here are few SQL examples showcasing the utilization of COUNT, SUM, and MAX SQL functions.

Example 1 - Raw data from employee table

Example 1 - COUNT

SELECT COUNT(*)
FROM org_employee;

Example 1 - Query data mapping

Essentially, you are required to determine the count of rows in the table.

Example 1 - Query Output

Example 2 - COUNT DISTINCT

SELECT COUNT(DISTINCT department)
FROM org_employee;

Example 2 - Query data mapping

The green-colored boxes that have been highlighted represent unique department names that will be considered for the count.

Example 2 - Query Output

Example 3 - SUM by categroy using GROUP BY

SELECT department
    SUM(salary)
FROM org_employee
GROUP BY department
ORDER BY SUM(salary) DESC;

Example 3 - Query data mapping

The salaries from each department are aggregated by department, summing them together.

Example 3 - Query Output

Example 4 - MAX by categroy using GROUP BY

SELECT department
    MAX(salary)
FROM org_employee
GROUP BY department; 

Example 4 - Query data mapping

Green-colored boxes represent the highest salary from each corresponding department.

Example 4 - Query Output