Select SQL Command | Aggregate Functions with Example

The most important part of Database Management is the retrieval of the correct data in the most efficient way. The query for data retrieval is the select SQL command. This makes the select query one of the most important queries in SQL.

Pre-requisite: This tutorial is about MySQL database. If you have not used MySQL database earlier, check complete MySQL tutorial.

Select Query Syntax

The basic syntax of the Select statement is –

Select <attribute_list> and <function_list>
from <table_list>
group by <grouping_attribute>
having <group_condition>
order by <attribute_list>;

We will discuss all of the keywords ones by one.

Select Query Example

Take an instance, Employee is the table in the database which contains column attributes as an employee unique id (E_id), employee name (E_Name), employee monthly salary (salary), employee department D_number, etc…

The simplest possible Select query is:

Select E_name from Employee;

Here, the E_name column of the Employee table will be displayed.

Write a SQL query to select multiple columns.

Select E_id, E_name from Employee;

Write a SQL query to select all columns.

Select * from Employee;

Note: Here asterisk (*) denotes all.

Aggregate Functions in Select SQL Command

There are many aggregate functions available in SQL. They include –

• Max() – Returns the maximum value.
• Min() – Returns the minimum value.
• Avg() – Returns the average value.
• Count() – Returns the number of rows.
• Sum() – Returns the sum of all values.

Within the parenthesis, we write the name of the column on which we want the aggregate function to operate.

For e.g.:

Select max(salary) from Employee;

We can also include all (*) as a parameter.

For e.g.:

Select count(*) from Employee;

Filters in SQL Query:

‘where’ condition:

To give a particular condition for selection of rows, we use ‘where’ in our select SQL command.

For e.g.:

Select E_name from Employee where salary>10000;

This statement selects the names of employees whose salary is more than 10,000.

Group by:

This is used to group together tuples (rows) of a particular type.

You may want to view the tuples of people belonging to the same department together in your resultant table. For this, we can write –

Select E_name, D_number from Employees group by D_number;

To view the number of Employees in a department and the average salary of the department, we use the following query–

Select D_number, count(*), avg(salary) from Employee group by D_number;

Having Clause:

This clause contains the group condition.

This means that the grouping is done first and then this condition is applied to retrieve the final results.

For e.g.: In the following query,

Select D_number, count(*), avg(salary) from Employee group by D_number having count(*)>2;

First, groups are created according to the department number, the count is calculated for each department and then it is checked if the department has more than two employees.
If it has more than two employees, then it is retrieved in the result set.

ORDER BY Clause in Select SQL Command

This is used to arrange the tuples in a particular order. This order can be numerical or alphabetical.

For e.g.:

Select E_id, E_name from Employee order by E_id;

This query orders the list as per the ascending order of employee id (numerical) whereas

Select E_id, E_name from Employee order by E_name;

This query orders the list as per the ascending order of E_name (alphabetical).

By default, everything is ordered in ascending order.

To define if the order is ascending or descending in the case of both alphabets and numbers, we use ASC and DESC.

For e.g.:

Select E_id, E_name from Employee order by E_name DESC;

This select SQL command retrieves the Employee name and Employee id with the Employee name arranged in reverse alphabetical order.

That is all about the most important select SQL commands. This creates a strong foundation to learn further concepts. Other queries shall be discussed in the forthcoming posts.

Heena Rajpal

Heena Rajpal is pursuing Computer Science Engineering from Indore. She has a knack for writing and an inquisitive outlook towards Computer Science fields like Database Management Systems, Object Oriented Programming and languages like C, C++, JAVA, Python, HTML etc.