Home Sql SQL Aggregate Functions In Where Clause

Related Tutorials


 
 

Share on Google+Share on Google+

SQL Aggregate Functions In Where Clause

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

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           |
+-------------+
Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  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.

Posted on: December 31, 2008

Related Tutorials

Discuss: SQL Aggregate Functions In Where Clause  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
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);
DMCA.com