COUNT Function
The COUNT
function returns a count of rows based on certain criteria. The syntax is: 1.COUNT(ALL or DISTINCT expression) or COUNT(*)If you specify a "*" as the criterion, COUNT returns the total number of rows in a table; for example, the following query counts rows in the FactFinance table of Adventure Works DW database:
1.SELECT COUNT(*)2.FROM FactFinance 3. 4.Results: 5.----------- 6.39409If you join multiple tables then COUNT(*) returns the number of rows satisfying the join criterion, as in the following:
1.SELECT COUNT(*)2.FROM FactFinance a INNER JOIN DimOrganization
b 3.ON a.OrganizationKey = b.OrganizationKeyUnlike all other aggregate functions, COUNT does not ignore NULL values.
If you need to find the count of unique items within a column in a table use COUNT (DISTINCT column_name) syntax. For example, the following query counts unique organization keys within the FactFinance table:
1.SELECT COUNT(DISTINCT OrganizationKey)2.FROM FactFinance 3. 4.Results: 5.----------- 6.9
COUNT_BIG Function
The
COUNT_BIG function is identical to the COUNT function, but returns a BIGINT
data type, whereas COUNT returns an INT. The upper limit for INT data type is 2
billion; therefore if you anticipate counting more than two billion rows use
the COUNT_BIG function. Any attempt to use the COUNT function when counting
over 2 billion rows returns an error.
MAX Function
The MAX
function returns the biggest value within a given set. The syntax is: 1.MAX(ALL
or DISTINCT expression)The ALL keyword is optional and is the default unless DISTINCT is specified. The DISTINCT keyword specifies that each unique value should be considered. This keyword really has no use with MAX function since it returns a single value; however the keyword is supported for ANSI compatibility.
For example, the following returns the greatest amount from the FactFinance table:
1.SELECT2.MAX(amount)3.FROM FactFinance 4.Results: 5.---------------------- 6.4820988Note that the parameter of MAX can be any valid expression, including string columns, as in the following:
1.SELECT2.MAX(EnglishProductName)3.FROM DimProduct01.SELECT02.EnglishProductName 03.FROM DimProduct 04.WHERE LEN(EnglishProductName) = (05. SELECT
MAX(LEN(EnglishProductName))06. FROM
DimProduct) Results:
EnglishProductName 07.--------------------------------------------------
08.ML Mountain Frame-W -
Silver, 40 09.ML Mountain Frame-W -
Silver, 42 10.ML Mountain Frame-W -
Silver, 46 11.ML Mountain Frame-W -
Silver, 38
MIN Function
The MIN
function returns the smallest value within a given set. The syntax is: 1.MIN(ALL
or DISTINCT expression)The ALL keyword is optional and is the default unless DISTINCT is specified. The DISTINCT keyword specifies that each unique value should be considered. This keyword really has no use with MIN function since it returns a single value; however the keyword is supported for ANSI compatibility.
For example, the following returns the least amount from the FactFinance table:
1.SELECT2.MIN(amount)3.FROM FactFinance 4.Results: 5.---------------------- 6.-1121918Note that the parameter of MIN can be any valid expression, including string columns, as in the following:
1.SELECT2.MIN(EnglishProductName)3.FROM DimProduct 4. 5.Results: 6.--------------------------------------------------
7.Adjustable RaceNote that "Adjustable Race" is returned because it is the first value in the alphabetically ordered list of product names, not because it is the shortest product name. You can combine MIN function with LEN function to return the products with the shortest names, as follows:
01.SELECT02.EnglishProductName 03.FROM DimProduct 04.WHERE LEN(EnglishProductName) = (05.SELECT MIN(LEN(EnglishProductName))06.FROM DimProduct) 07. 08.Results: 09.EnglishProductName 10.--------------------------------------------------
11.Stem
AVG Function
The AVG
function returns the average of the values within a column. Unlike MIN and MAX,
AVG can only accept a numeric expression as a parameter. The syntax is: 1.AVG(ALL
or DISTINCT numeric column)ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value.
The following query returns the average amount from FactFinance table:
1.SELECT2.AVG(Amount)3.FROM FactFinance 4. 5.Results: 6.----------- 7.34475.3841178411The next example uses DISTINCT keyword to return the average of distinct values:
1.SELECT2.AVG(DISTINCT
Amount)3.FROM FactFinance 4. 5.Results: 6.----------- 7.95592.8708615292The AVG function returns the same (or a similar) data type as the group of values it accepts. So if you pass an integer, expect an integer back; if you examine float values with the AVG function you will get a float value back.
SUM Function
The SUM
function returns the sum of all or unique values. Unlike MIN and MAX, SUM can
only accept a numeric expression as a parameter. The syntax is: 1.SUM(ALL
or DISTINCT numeric column)ALL keyword is optional and is assumed by default. You can specify the DISTINCT keyword if you want to take into account only the unique occurrence of each value.
The following query returns the sum of amounts from FactFinance table:
1.SELECT2.SUM(Amount)3.FROM FactFinance 4. 5.Results: 6.----------- 7.1358640412.7The next example uses DISTINCT keyword to return the sum of distinct values:
1.SELECT2.SUM(DISTINCT
Amount)3.FROM FactFinance 4. 5.Results: 6.----------- 7.1251597458.19The SUM function returns the same (or a similar) data type as the group of values it accepts. So if you pass an integer, expect an integer back; if you examine float values with the SUM function you will get a float value back.
No comments:
Post a Comment