SQL Single Row Functions

Single Row Fuctions:

Single row functions are also called aggregate functions.

Single row functions return only one row when they are executed.

Sample Table

Doctor_id	Name	Fee
D101	John	10000
D102	Peter	20000
D103	Nash	30000
D104	Andrew	25000
D105	Smith	15000

1

select sum(fee) as fee_total from doctor

Sum function will return the total fee from all rows

fee_total

100000

2

Min function returns the smallest value from all rows for the column specified

select min(fee) as minimum_fee from doctor

    

minimum_fee

10000

    

3

Max function returns the maximum value from all rows for the column specified

select max(fee) as maximum_fee from doctor

maximum_fee

30000

4

Count function returns the total number of rows in the table

select count (doctor_id) as total_doctors from doctor

  

total_doctors

5

    

5

Avg() function return the average from row specified.Avg() functions --is applied on numeric values

select avg(fee)as Average_Fee from doctor

Average_Fee

20000

Some Other Functions:

1

Top function returns top rows which you specify in sql statement

select top  3 * from doctor

Returns top 3 rows

Doctor_id	Name	Fee
D101	John	10000
D102	Peter	20000
D103	Nash	30000

2

Upper() function converts the column value to lower case it is appled on character values

select name,upper(name) as Uppercase from doctor

Name	Uppercase
Andrew	ANDREW
John	JOHN
Nash	NASH
Peter	PETER
Smith	SMITH

3

Lower() function converts the column value to upper case applied on charcter values

select name,lower(name) as Lowercase from doctor

Name	Lowercase
Andrew	andrew
John	john
Nash	nash
Peter	peter
Smith	smith

4

Left function selects left words of a column.It takes two argument first is column name and second is number of words to select from column.

select doctor_id,left(doctor_id,2)as leftwords from doctor

This will select two words from left of doctor_Id

Doctor_Id	Leftwords
D101	D1
D102	D1
D103	D1
D104	D1
D105	D1

Similarly you can select words from right side of a column like this.

select doctor_id,right(doctor_id,2)as rightwords from doctor

5

Substring function returns a substirng from a string.It takes 3 arguments

1 .Column Name or string

2.Starting Position

3.Ending Position

select name, substring(name,1,3) as substring from doctor

This will select a substring for each row from name column starting from 1^st  position to 3^rd position.

Name	substring
Andrew	And
John	Joh
Nash	Nas
Peter	Pet
Smith	Smi

6

Charindex function returns the index of a specific character from a string. It takes two arguments

1. Character for which index is required

2. Column Name or string

select name,charindex('n',name) as IndexNo from doctor

This will get index for character ‘n’ from name column for every row.The row which does not contain character ‘n’ 0 will be the charindex from those rows.

Name	IndexNo
Andrew	2
John	4
Nash	1
Peter	0
Smith	0

7

Reverse function reverses a string. It takes only one argument which is any column you want to reverse or you can specify a string as argument.

select name,reverse(name)as Reverse Name from doctor

This will reverse name column values.

Name	Reverse Name
Andrew	werdnA
John	nhoJ
Nash	hsaN
Peter	reteP
Smith	htimS

8

Len function gets length of a string in as total number of characters in the string.

select name, len(name) as NoOfCharacters from doctor

This will get total number of characters in from the name column for each row.

Name	NoOfCharacters
Andrew	6
John	4
Nash	4
Peter	5
Smith	5

9

Replace function replaces an existing word to a new word. It requires three arguments

1. Column name or string

2. Character to be replaced

3. New Character to be placed

select name, replace(name,'a','e') as ReplacedCharacter from doctor

This will replace the character ‘a’ from every where ‘a’ exists in name column with character ‘e’ .

Name	ReplacedCharacter
Andrew	endrew
John	John
Nash	Nesh
Peter	Peter
Smith	Smith

10
To remove spaces from left or right from a column trim function is used.

Ltrim(column name) This removes spaces from left

Rtrim(column name) This removes spaces form right.

select name, ltrim(name) as RemovedSpacesLeft from doctor

select name, rtrim(name) as RemovedSpacesright from doctor