SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would be:

SELECT COUNT (*) FROM employee
WHERE dept = 'Electronics';

The output would be '2' rows.

If you want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM employee;

The output would be '5' rows.

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from employee table, the query would be:

SELECT DISTINCT dept FROM employee;

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM employee;

SQL MAX(): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an employee, the query would be:

SELECT MAX (salary) FROM employee;

SQL MIN(): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an employee, he query would be:

SELECT MIN (salary) FROM employee;

SQL AVG(): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM employee;

SQL SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the employees,

SELECT SUM (salary) FROM employee;

SQL NULL Tutorial

SQL NULL value represents a blank value in a table. NULL value is used for identifying any missing entries in a table.

One can use NOT NULL value to display table entries which are not NULL.

Syntax of NULL Value

SELECT column_name(s)
FROM table_name
Where column_name IS NULL;

Note: "Location" column in the "Employee" table above is optional. Therefore, if one enters a data with no value for the "Location" column, the "Location" column will be saved with a NULL value.

Now if one wants to display the entries whose location is left blank, then here is a statement example.

SELECT * FROM Employee
WHERE Location IS NULL;

SQL NULL Statement Output:

The NULL statement will display the following results

Using SQL LIKE

The LIKE operator is used to select the ones that fit the criteria we specify from the records in our table.

LIKE Usage SELECT field_name(s) FROM table_name WHERE to query_field_name LIKE query_value LIKE is an operator and is used with WHERE. That is, it is used instead of the equal, greater or less than sign.

id Name_surname City Department Job_Code

1 XXXXXXX Istanbul Data Processing Officer 1234567

 2 XXXXXX Kocaeli Administrative Affairs Manager 2345678

 3 XXXXXXX Istanbul Finance Manager 3456789

Example1:

SELECT * FROM Staff

WHERE City LIKE 'I%'

The point to note here is that the % sign is used after the LIKE statement. In this example, records starting with the letter I are selected in the City field. The % sign represents the remaining character after the letter I. So this query means: Select records starting with letter I from data in City field. Output: id Name_surname City Department Job_Code

1 XXXXXXX Istanbul Data Processing Officer 1234567

3 XXXXXXX Istanbul Finance Manager 34567

Next