Boost Performance and Reduce Code Use With SQL Server Aggregate Functions

This query fetches the following result.

Type

Tot_Num

—–

——-

GSM

8

CDMA

8

3G

4

The primary rule for using the GROUP BY clause is to make sure all the columns in your select list are present in your GROUP BY clause.

SELECT Type, Make, COUNT(Type) AS Tot_Num
FROM Mobiles_phones
GROUP BY Type, Make

This query gives you the following result.

Type

Model

Tot_Num

—-

—–

——-

GSM

Nokia

3

CDMA

Nokia

2

3G

Nokia

2

Suppose you wanted to list all the mobile phone types that have a minimum of four entries. You might think of using a WHERE clause, but remember that you should not use aggregate functions in the WHERE clause. Instead use the HAVING clause.

SELECT Type, COUNT (Type) AS Tot_Num
FROM Mobiles_phones GROUP BY Type
HAVING COUNT (Type) > 4

This query will give you the following result.

Type

Tot_Num

—-

——-

GSM

8

CDMA

8



Essential Aggregate Functions

Aggregate functions are acceptable as expressions in the following:

  • The select list of a SELECT statement.
  • A COMPUTE or COMPUTE BY clause.
  • A HAVING clause.

Transact-SQL has seven key aggregate functions. We will use the table below to explain the basic uses of these functions.

Name

Age

Salary

Alex

34

11000

Bob

23

20000

Cathy

22

12500

Dona

19

35000

James

21

16000

Kelly

45

21550

Mosses

35

20600

Nancy

21

16000

Peter

23

16000

Rosalie

32

10000

Samuel

45

11000

1. MAX (column)

The MAX function returns the highest value of a selected column. It does not include NULL values in the calculation.

Syntax:

SELECT MAX (column) FROM Table

Example:

SELECT MAX (salary) FROM Employee

Result: 35000.

2. MIN (column)

The MIN function returns the lowest value of a selected column. It does not include NULL values in the calculation.

Syntax:

SELECT MIN (column) FROM Table

Example:

SELECT MIN (salary) FROM Employee

Result: 11000.

Continues…

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |