Share on Google+Share on Google+

SQL Aggregate Functions In Where Clause

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

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);


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        |


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');


| max(stu_id) |
| 5           |


Posted on: December 31, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

santosh ippili
January 11, 2012
how we can use split function on 'ON' condition

SELECT C.Id 'CustInteractId',P.Name 'Participant',P.Id 'ParticipantId' INTO #Temp8 FROM dbo.CustomerInteractions C INNER JOIN Participants P ON 1=1; SELECT * FROM #Temp8; DROP TABLE #Temp8; DECLARE @Names NVARCHAR(500); SELECT @Names = [Participants] FROM Sheet$ WHERE [Sr# No#] = 1; PRINT @Names; --SELECT * FROM dbo.UTILfn_Split(@Names,','); ----Below 2 stmts any stmt can work... select * from dbo.FN_ListToTable(',',@Names); SELECT T.Participant,T.CustInteractId,T.ParticipantId INTO #Temp9 FROM Sheet$ S INNER JOIN #Temp8 T ON T.Participant = select * from dbo.FN_ListToTable(',',@Names);