SQL Aggregate Functions In Where Clause

SQL Aggregate Function In Where Clause return the maximum conditional value of the record from a table.

Understand with Example

The Tutorial help you to understand an Example from 'SQL Aggregate Functions'. To understand this, we create a table 'Stu_Table'. The create table construct a table 'Stu_Table' with different Table attribute specified in the query.  

Next, we might want to conditionally select the data from a table. For example, we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:

Create the Table Stu_Table

SQL statement to create table:

create table Stu_Table(Stu_Id integer(2), Stu_Name varchar(15), Stu_Class  varchar(10));

Insert data into Stu_Table

Insert into keywords in SQL add the records or rows to the table 'Stu_Table'.

SQL statement to insert data into table:

insert into Stu_Table values(1,'Komal',10);
insert into Stu_Table values(2,'Ajay',10);
insert into Stu_Table values(3,'Rakesh',10);
insert into Stu_Table values(4,'Bhanu',10);
insert into Stu_Table values(5,'Santosh',10);
insert into Stu_Table values(6,'Tanuj',10);

Stu_Table

Records in the table:

+--------+----------+-----------+
| Stu_Id | Stu_Name | Stu_Class |
+--------+----------+-----------+
| 1      | Komal    | 10        |
| 2      | Ajay     | 10        |
| 3      | Rakesh   | 10        |
| 4      | Bhanu    | 10        |
| 5      | Santosh  | 10        |
| 6      | Tanuj    | 10        |
+--------+----------+-----------+

Query

Next, the given below Query want to return the conditionally select the data from a table 'Stu_Table'. For example, we want to retrieve the maximum value of the 'stu_id' with name search specified in the 'Stu_Name'. In order to overcome, we use the WHERE clause. The Syntax is given below: 

select max(stu_id) from stu_table 
where stu_name in('komal','ajay','santosh','rakesh');

Result

+-------------+
| max(stu_id) |
+-------------+
| 5           |
+-------------+