Chapter 4 SQL aggregate function COUNT (2)

jurisdiction

To use COUNT(*), you must have table level SELECT permissions on the specified table.
To use count (column name), you must have column level SELECT permission on the specified column or table level SELECT permission on the specified table.
You can determine whether the current user has SELECT permission by calling the% CHECKPRIV command.
You can call $system SQL. Security. The checkprivilege() method to determine whether the specified user has table level SELECT permissions.

performance

For optimal COUNT performance, you should define indexes as follows:

• For COUNT(*), define the bitmap extension index if necessary.
  This index may be automatically defined when the table is created.
• For COUNT(fieldname), define a slice index for the specified field.
  Query plan optimization COUNT(fieldname) automatically applies the default collation to fieldname.

Changes made by uncommitted transactions

Like all aggregate functions, COUNT always returns the current state of data, including uncommitted changes, regardless of the isolation level of the current transaction, as shown below:

• COUNT count of records inserted and updated, even if these changes have not been committed and may be rolled back.
• COUNT does not calculate deleted records, even if they have not been committed and can be rolled back.

Example

The following example returns sample Total rows in person:

SELECT COUNT(*) AS TotalPersons
     FROM Sample.Person

The following example returns sample Count of names, spouses, and favorite colors in person. These counts are different because some spouses and FavoriteColors fields are NULL; Count does not compute NULL:

SELECT COUNT(Name) AS People,
       COUNT(Spouse) AS PeopleWithSpouses,
       COUNT(FavoriteColors) AS PeopleWithColorPref
FROM Sample.Person

The following example returns three values: the total number of rows, the total number of non null values in the FavoriteColors field, and the total number of different non null values in the FavoriteColors field:

SELECT COUNT(*) As TotalPersons,
       COUNT(FavoriteColors) AS WithColorPref,
       COUNT(DISTINCT FavoriteColors) AS ColorPrefs
       FROM Sample.Person

The following example uses count DISTINCT to return sample Count of different FavoriteColors values in person. (FavoriteColors contains multiple data values and multiple nulls.). This example also uses the DISTINCT clause to return a row for each different FavoriteColors value. The row count is 1 greater than the COUNT(DISTINCT FavoriteColors) count because DISTINCT returns a single null row as the DISTINCT value, but count DISTINCT does not calculate null. The COUNT(DISTINCT BY(FavoriteColors)%ID) value is the same as the row count because the BY clause counts a single null as a DISTINCT value:

SELECT DISTINCT FavoriteColors,
       COUNT(DISTINCT FavoriteColors) AS DistColors,
       COUNT(DISTINCT BY(FavoriteColors) %ID) AS DistColorPeople
FROM Sample.Person

The following example uses GROUP BY to return a row for each FavoriteColors value, including a row of NULL. Associated with each row are two counts. The first uses the FavoriteColors option to calculate numbers or records; Empty records are not calculated. The second calculates the number of names associated with each favoritecor selection; Since Name does not contain a NULL value, you can use a NULL value to calculate the number of favorable colors:

SELECT FavoriteColors,
       COUNT(FavoriteColors) AS ColorPreference,
       COUNT(Name) AS People
       FROM Sample.Person
       GROUP BY FavoriteColors

The following example returns sample Each home in person_ Person record count for state value:

SELECT Home_State, COUNT(*) AS AllPersons
     FROM Sample.Person
     GROUP BY Home_State

The following example uses% AFTERHAVING to return the personal record count and the number of people over 65 for each state where at least one person is over 65:

SELECT Home_State, COUNT(Name) AS AllPersons,
     COUNT(Name %AFTERHAVING) AS Seniors
     FROM Sample.Person
     GROUP BY Home_State
     HAVING Age > 65
     ORDER BY Home_State

The following example uses both the% FOREACH and% AFTERHAVING keywords. It returns A row for the state of A person whose name begins with "A", "M", or "W" (HAVING clause and GROUP BY clause). Each status line contains the following values:

• Count(Name): the count of all persons in the database. (this number is the same for all rows.)
• Count (name% foreach (home_state)): count of owners in the state.
• Count (name% after HAVING): count of all persons in the database that meet the conditions of the HAVING clause. (this number is the same for all rows.)
• Count (name% foreach (home_state)% after HAVING: count of all persons in the state who meet the criteria of HAVING clause.

SELECT Home_State,
       COUNT(Name) AS NameCount,
       COUNT(Name %FOREACH(Home_State)) AS StateNameCount,
       COUNT(Name %AFTERHAVING) AS NameCountHaving,
       COUNT(Name %FOREACH(Home_State) %AFTERHAVING) AS StateNameCountHaving
FROM Sample.Person
GROUP BY Home_State
HAVING Name LIKE 'A%' OR Name LIKE 'M%' OR Name LIKE 'W%'
ORDER BY Home_State

The following example shows a Count with a concatenation expression. It uses the join operator (|) to return the total number of non null values in the FavoriteColors field and the total number of non null values connected to the other two fields in FavoriteColors:

SELECT COUNT(FavoriteColors) AS Color,
       COUNT(FavoriteColors||Home_State) AS ColorState,
       COUNT(FavoriteColors||Spouse) AS ColorSpouse
       FROM Sample.Person

When two fields are connected together, COUNT calculates only those rows in which neither field has a NULL value. Because sample Each line in person has a non empty Home_State value, so concatenate FavoriteColors | Home_State returns the same COUNT as FavoriteColors. Because sample The spouse value of some rows in person is NULL, so concatenating FavoriteColors 𞓜 spoport will return the number of rows with non NULL values of FavoriteColors and spouse.