SQL Aggregate Function - Syntax and Example

SQL Aggregate Function is built-in functions for counting and calculation (perform a calculation on a set of values and return a single value)
Syntax for built-in SQL functions is - SELECT function(column) FROM table

Name	Salary
Emil	5000
Chang	5000
Emily	4500
Nick	4000

Table Name: A


Below is Example for SQL built-in Function , together with SQL Statement and the answer.

AVG - Average value of columns
Select AVG(Salary) FROM A
Value = 4675

COUNT - number of rows
Select COUNT(*) FROM A
Value = 4

SELECT COUNT (DISTINCT Salary) FROM CUSTOMERS
Value = 3

MAX - Maximun or Highest number in a column
SELECT MAX(Salary) FROM CUSTOMERS
Value = 5000

MAX - Minimum or Lowest number in a column
SELECT MIN(Salary) FROM CUSTOMERS
Value = 4000

SUM - Total number in a column
SELECT SUM(Salary) FROM CUSTOMERS
Value = 18500

SQL DATENAME Function - Year, Quarter, Month, Day, Hour, Minute and Milisecond.

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond.

SQL DATENAME Syntax
DATENAME ( datepart , date )

Example

SELECT GETDATE() = 2007-05-30 23:13:38.763

SELECT DATENAME(year, GETDATE())
SELECT DATENAME(yy, GETDATE())
SELECT DATENAME(yy, GETDATE())
- It will return value = 2007

SELECT DATENAME(quarter, GETDATE())
SELECT DATENAME(qq, GETDATE())
SELECT DATENAME(q, GETDATE())
-It will return value = 2 (because 1 quarter equal to 3 month,Detail see below table)

Month	Quarter Value
January - March	1
April - June	2
July - September	3
October - December	4

SELECT DATENAME(month, GETDATE())
SELECT DATENAME(mm, GETDATE())
SELECT DATENAME(m, GETDATE())
- It will return value = May

SELECT DATENAME(dayofyear, GETDATE())
SELECT DATENAME(dy, GETDATE())
SELECT DATENAME(y, GETDATE())
- It will return value = 150 (this is calculate total day from 1 jan 2007 until 30 may 2007)

SELECT DATENAME(day, GETDATE())
SELECT DATENAME(dd, GETDATE())
SELECT DATENAME(d, GETDATE())
- It will return value = 30

SELECT DATENAME(week, GETDATE())
SELECT DATENAME(wk, GETDATE())
SELECT DATENAME(ww, GETDATE())
- It will return value = 23 (this is 23rd week from 1 jan 2007)

SELECT DATENAME(hour, GETDATE())
SELECT DATENAME(hh, GETDATE())
- It will return value = 23 (time for 24 hour)

SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(mi, GETDATE())
SELECT DATENAME(n, GETDATE())
- It will return value = 13 (minute)

SELECT DATENAME(second , GETDATE())
SELECT DATENAME(ss, GETDATE())
SELECT DATENAME(s, GETDATE())
- It will return value = 38 (second)

SELECT DATENAME(millisecond , GETDATE())
SELECT DATENAME(ms, GETDATE())
- It will return value = 763 (milisecond)

SQL Inner Join, Left Join and Right Join

Below is a Example for SQL inner join, SQL Left Join and SQL Right Join.

GuestID	Name
1001	Nick
1002	Jack
1003	Apple

Table A

Product	GuestID
Table	1002
Chair	1003
Almari	1002

Table B

SQL inner join
Example for SQL inner join
- Join more than 1 table to get other table data
1. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID
Result :

Name	Product
Jack	Table
Apple	Chair
Jack	Almari

2. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID WHERE B.Product = 'tABLE'

Result:

Name	Product
Jack	Table

Get Number of Row in Select Statement

For SQL Server 2000, you can using below SQL Query or SQL statement to get number of row for a SELECT Statement.

SELECT rank=COUNT(*), A1.countryname, A1.countrycode FROM country A1, country A2 WHERE A1.countryname + A1.countrycode >= A2.countryname + A2.countrycode GROUP BY A1.countryname, A1.countrycode ORDER BY rank

Table (Before using the above SQL Query): SELECT * FROM TABLE

Table (After using the above SQL Query): It add 1 more column to show the number of Row for this table.

For SQL Server 2005, you can using below SQL Query or SQL statement to get number of row for a SELECT Statement.

SELECT rank() OVER (ORDER BY A1.countryname, A1.countrycode) as rank, A1.countryname, A1.countrycode FROM country A1 ORDER BY rank

The result will same as above image.

For more information about SQL Query Syntax or SQL statement Syntax,click here
For more information about Example of SQL Query or SQL statement,click here

Select Statement for Get Number of Days in a Month

Using SQL statement or SQL Query to calculating the Number of Days in a Month

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

Example on may(month), you just copy and paste the SQL Query or SQL statement (Above blue color) to the SQL Query Analyzer. You will get the answer is 31 days for May(month).

SQL Statement Example for SELECT, UPDATE and DELETE

Below is Sql Statement with Example provided. It will help SQL beginner more understanding about the usage of SQL statement. For more information about SQL Syntax, click here.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	CEO
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

Table Name : Testing

SQL STATEMENT - Example Result with SQL Statement
Select All data from a table (same as above table)- SELECT * FROM TESTING

SELECT NAME,COMPANY FROM TESTING

name	company
Angle	Ajax Tech Limited
Helen	Programmingschools Limited
Micheal	SQLTutorial Limited
Nick	Programmingschools Limited

SELECT DISTINCT COMPANY FROM TESTING

company
Ajax Tech Limited
Programmingschools Limited
SQLTutorial Limited

SELECT COUNT(*) FROM TESTING

4

SELECT * FROM TESTING WHERE NAME='HELEN'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO

SELECT * FROM TESTING WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO

SELECT * FROM TESTING WHERE NAME='HELEN' OR COMPANY='Pragrammingschools Limited'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer

SELECT * FROM TESTING WHERE COMPANY IN ('Pragrammingschools Limited')

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer

SELECT * FROM TESTING WHERE COMPANY LIKE '%PROGRAMMINGSCHOOLS%'

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer

SELECT * FROM TESTING WHERE ORDER BY COMPANY

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer
Micheal	4000	SQLTutorial Limited	DBA

SELECT * FROM TESTING WHERE ORDER BY COMPANY DESC

Name	Income	Company	Position
Micheal	4000	SQLTutorial Limited	DBA
Helen	6900	Programmingschools Limited	CEO
Nick	5000	Programmingschools Limited	Programmer
Angle	4500	Ajax Tech Limited	Manager

SELECT SUM(INCOME),COMPANY FROM TESTING GROUP BY COMPANY

SUM(Income)	Company
4500	Ajax Tech Limited
11900	Programmingschools Limited
4000	SQLTutorial Limited

SELECT COUNT(*),COMPANY FROM TESTING GROUP BY COMPANY

COUNT(*)	COMPANY
1	Ajax Tech Limited
2	Programmingschools Limited
1	SQLTutorial Limited

SELECT SUM(INCOME),COMPANY FROM TESTING GROUP BY COMPANY HAVING (SUM(INCOME)>5000)

SUM(INCOME)	COMPANY
11900	Programmingschools Limited

For Sql Update and Sql Delete statement, click here

SQL Statement Example for UPDATE and DELETE

UPDATE Statement
UPDATE statement is used to edit and modify the data in a table. Below showing the example for Update and Delete Statement.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	CEO
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

Table Name: Testing

SQL Statement With Example

UPDATE TESTING SET POSITION='BOSS' WHERE NAME='HELEN'
- Position Column updated from CEO to BOSS using the update statement with 1 condition name=helen.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	BOSS
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

UPDATE TESTING SET POSITION='Head of Department' WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited'
- Position Column updated from BOSS to Head of Department using the update statement with 2 condition name=helen and company=Programmingschools Limited.

Name	Income	Company	Position
Angle	4500	Ajax Tech Limited	Manager
Helen	6900	Programmingschools Limited	Head of Department
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

DELETE Statement
DELETE FROM TESTING WHERE NAME='Angle'
-Delete row using delete statement with condition name=angle.

Name	Income	Company	Position
Helen	6900	Programmingschools Limited	Head of Department
Micheal	4000	SQLTutorial Limited	DBA
Nick	5000	Programmingschools Limited	Programmer

DELETE FROM TESTING
-Whole table deleted


For more information for SELECT Statement, click here

All Topic in Sql Tutorials and ProgrammingSchools

sqltutorials

• SQL Syntax - SELECT,UPDATE and DELETE
• SQL Statement Sample for SELECT, UPDATE and DELETE
• SQL Statement Sample for UPDATE and DELETE

programmingschools

• Asp Net & Javascript Avoid Multiple Submit (Only Allow Submit 1 Time)
• META http-equiv Auto Refresh every 10 second
• ASP NET AJAX Coding for Refresh Date Time without Refresh
• Programming : Create Cookies Cross Domain
• Programming Tip: How to know Google had indexing your site?
• Programming : Get All the Url on a Web Page - VB.net

SQL Syntax - SELECT,UPDATE and DELETE

Below is the Lists of possible syntax for SELECT,UPDATE and DELETE

SELECT Syntax - Get Information from a table in database
SELECT ColumnName1,ColumnName2, ColumnName3 FROM TableName
SELECT DISTINCT ColumnName FROM TableName
SELECT COUNT (ColumnName) FROM TableName
SELECT ColumnName FROM TableName WHERE ColumnName = value
SELECT ColumnName FROM TableName WHERE ColumnName1 = value AND ColumnName2 = value
SELECT ColumnName FROM TableName WHERE ColumnName1 = value OR ColumnName2 = value
SELECT ColumnName FROM TableName WHERE ColumnName IN (Value1,Value2)
SELECT ColumnName FROM TableName WHERE ColumnName BETWEEN (Value1 AND Value2)
SELECT ColumnName FROM TableName WHERE ColumnName Like '%Value1%'
SELECT ColumnName FROM TableName ORDER BY ColumnName
SELECT ColumnName1 , SUM(ColumnName2 ) FROM TableName GROUP BY ColumnName1 HAVING (Arithematic Condition)

UPDATE Syntax
UPDATE TableName SET ColumnName = Value WHERE {Condition}
UPDATE TableName SET ColumnName = Value WHERE ColumnName = value

DELETE Syntax
DELETE TableName WHERE {Condition}
DELETE TableName WHERE ColumnName = value

What is SQL?

SQL (Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL works with database programs like MS SQL Server, MS Access, MSDE, MySQL, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

SQL Data Manipulation Language (DML)
DML is a syntax for executing queries and DML component of SQL comprises have four basic statements:
SELECT - Retrieve rows from tables
UPDATE - Modify the rows of tables
DELETE - Remove rows from tables
INSERT - Add new rows to tables.

SQL Data Definition Language (DDL)
DDL is used to create and destroy databases, database table and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. DML component of SQL comprises have four basic statements:
CREATE - Creates a new database table
ALTER - Alters / changes a database table
DROP - delete a database table