Understanding SQL Aggregate Functions (USAF)


USAF:   SOLD       



Note: If you do not have a database, as well as an application to support the practicum, then don't worry, some of these tools are not big, please download them here:

In returning the value of data, several query calculations are required, which are expressed by aggregate functions in the form of SQL commands, including the following:

1. COUNT

The COUNT command is used to count the number of data in a table.

Below is an example of using the COUNT command to count the number of data in the employees table (don't forget to install the Northwind database first).

SELECT COUNT(*) FROM employees;


Executing the COUNT Command

2. SUM

Used to return the total value of a specified column.

Now we will try to use the SUM command to calculate the total products that are still available.

SELECT SUM(UnitsInStock) FROM products;


Execute SUM Command

3. AVG

Used to return the average value of a specified column.

For example, we will try to find out about the average number of products ordered by most customers.

SELECT AVG(Quantity) FROM order_details;


AVG Command Execution

As per the results above, we can conclude that the average number of products ordered by customers ranges from 23 to 24 units.

4. MIN

The MIN command is used to return the smallest value from a specified column.

For example, let's use the command to find out the least stock of a product.

SELECT ProductName, UnitsInStock FROM products
WHERE UnitsInStock =
(
SELECT MIN(UnitsInStock) FROM products
);


MIN Command Execution

5. MAX

The MAX command is used to return the largest value from a specified column.

Still the same, regarding product stock, now we try to use the command to find the product with the most stock.

SELECT ProductName, UnitsInStock FROM products
WHERE UnitsInStock =
(
SELECT MAX(UnitsInStock) FROM products
);


MAX Command Execution

6. Additional Notes

1. MAX

Used to display the largest record value in a field.

Example:

Select max(saldo) as 'Saldo Tertinggi' from rekening

To use aggregate functions simultaneously, you can use a comma ( , )

Example:

Select max(saldo) as 'Saldo Tertinggi', min(saldo) as 'Saldo Terendah', avg(Saldo) as 'Saldo Rata-rata', count(saldo) as 'Jumlah' from rekening

MAX is part of the SQL aggregate functions, along with other functions such as SUM, AVG, COUNT and MIN.

2. MIN

Used to display the smallest record value in a field.

Example:

Select min(saldo) as 'Saldo Terendah' from rekening

Example:

Select max(saldo) as 'Saldo Tertinggi', min(saldo) as 'Saldo Terendah', avg(Saldo) as 'Saldo Rata-rata', count(saldo) as 'Jumlah' from rekening

MIN is part of the SQL aggregate functions, along with other functions such as SUM, AVG, COUNT and MAX.

3. COUNT

Used to count the number of records in a table.

Example:

Select count(*) as 'Jumlah Record' from nasabah

Example:

Select max(saldo) as 'Saldo Tertinggi', min(saldo) as 'Saldo Terendah', avg(Saldo) as 'Saldo Rata-rata', count(saldo) as 'Jumlah' from rekening

COUNT is part of the SQL aggregate functions, along with other functions such as SUM, AVG, MIN and MAX.

4. AVG

Used to find the average value of records in one field.

Example:

Select avg(saldo),min(saldo) from rekening

Example:

Select max(saldo) as 'Saldo Tertinggi', min(saldo) as 'Saldo Terendah', avg(Saldo) as 'Saldo Rata-rata', count(saldo) as 'Jumlah' from rekening

AVG is part of the SQL aggregate functions, along with other functions such as SUM, COUNT, MIN and MAX.

5. SUM

Used to add up the record values ​​in a field. This function can only be used on integer/numeric data.

Example:

Select sum(saldo) as 'Total Saldo' from rekening

SUM is part of the SQL aggregate functions, along with other functions such as AVG, COUNT, MIN and MAX.


Post a Comment

Previous Next

نموذج الاتصال