Using Functions (MAX, COUNT, MIN, AVG, SUM, CONCAT in SQL)

Written by

Soumya Shaw

If you believed that only conventional Computer languages only have features of Functions then, you might have missed the domain here! In addition, MySQL also has predefined functions that comes handy in usage.

We will discuss the Conventional functions only here so that the Querying process becomes Smooth and you can handle most of the Cases you get.

The following functions will be discussed in the following sections of the material.

  • SQL COUNT
  • SQL MAX
  • SQL MIN
  • SQL AVG
  • SQL SUM
  • SQL CONCAT

count in sql:

Going by the order, let’s start with the ‘Count’ function. This is used to count the Number of Rows whether used along with a condition or not. It is combined with the SELECT query.

General Syntax:

SELECT COUNT(attribute)

FROM table_name;

Okay, now let us find out the Number of entries in the country table by using the ‘COUNT’ function.

SELECT COUNT(*)
FROM country;

Now, if we want to know the Number of Workers in Air Force table who have Serving Time more than or equal to 35.

SELECT COUNT(*)
FROM air_force
WHERE serving_time>=35;

Note: There is no Space in between COUNT(*), otherwise it may show you an Error.

max in sql:

As the name suggests, MAX function also gives the Maximum value from that particular Attribute.

General Syntax:

SELECT MAX(attribute)

FROM table_name;

Examples:

SELECT MAX(population)
FROM country;

Similarly, if we want all the attributes to be shown along with the satisfying condition for the tuple, we can use this explicit query mentioned below.

SELECT *
FROM country
WHERE population=(SELECT MAX(population) FROM country);

min in sql:

The ‘MIN’ function gives out the Minimum value out of the Attribute provided.

General Syntax:

SELECT MAX(attribute)

FROM table_name;

Example:

SELECT *
FROM railways
WHERE costperkm<100
AND avg_delay=(SELECT MIN(avg_delay) FROM railways);

avg in sql:

The ‘AVG’ Function calculates the Average Value of the Attribute that you provide as an Argument.

General Syntax:

SELECT AVG(Attribute)

FROM table_name;

Example:

SELECT AVG(marks) Marks
FROM school;

Note: The Above Mentioned Syntax also shows how to name your own Column as “Marks” is selected here!

sum in sql:

General Syntax:

SELECT SUM(Attribute)

FROM table_name;

Example:

SELECT SUM(quantity) Total_Quantity
FROM store;

concat in sql:

The ‘CONCAT’ function performs the Concatenation of the Strings that are passed to the function.

General Syntax:

SELECT CONCAT(Attribute)

FROM table_name;

Example:

SELECT CONCAT(roll,name,marks,grade) Condensed_Form
FROM school;

Using Functions (MAX, COUNT, MIN, AVG, SUM, CONCAT in SQL)