Which function is used to COUNT the number of records?

In the formula bar, you can select each formula (A, B, and C), and press F9 to calculate that formula. This screen shot shows the results.

Then, calculate the A/B portion of the formula, to see these results. TRUE is equal to 1, so 1/1 equals 1, and 1/2 equals 0.5.

Next, calculate A/B - C, to see these results. TRUE is 1 and FALSE is zero, so 1-1 equals zero, and 0.5 - 0 equals 0.5:

Finally, the SUMPRODUCT function gives the sum of those numbers, with the result of 2.

Worksheet Formulas

You can see the same results if you put each part of the formula on the worksheet, and calculate each row separately.

In this screen shot, you can see the A and B results, then A/B. Next, see the C calculations, and A/B-C. At the bottom of the final column, the sum is shown.

Use the Formula Result

In other parts of your workbook, you can refer to cell A4 (DupIds), to create warning messages, or show a zero, instead of the expected results. For example, show a message with a formula like this:

Our database has a table named pet with data in the following columns: id, eID (electronic identifier), and name.

ideIDname123456sparky223457mily3NULLlessy4NULLcarl534545maggy

Let’s count all rows in the table.

Solution:

COUNT(*) counts the total number of rows in the table:

SELECT COUNT(*) as count_pet
FROM pet;

Here’s the result:

count_pet5

Instead of passing in the asterisk as the argument, you can use the name of a specific column:

SELECT COUNT(id) as count_pet
FROM pet;

In this case, COUNT(id) counts the number of rows in which id is not

SELECT COUNT(id) as count_pet
FROM pet;

0.

Discussion:

Use the

SELECT COUNT(id) as count_pet
FROM pet;

1 aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id) and returns the number of rows for this particular column in the table (e.g., 5).

As mentioned above, when you specify a column instead of the asterisk, the function will only count non-

SELECT COUNT(id) as count_pet
FROM pet;

0 values. Since id is the primary key of our table—and therefore has unique and non-

SELECT COUNT(id) as count_pet
FROM pet;

0 values—it’s a good candidate for counting the total number of rows in the table.

Of course, to count all rows, you can instead pass in the asterisk character as the argument to COUNT. This will count all rows, including those with a value of

SELECT COUNT(id) as count_pet
FROM pet;

0 in any column.

Here’s an example of counting the number of rows for a column that has

SELECT COUNT(id) as count_pet
FROM pet;

0 values:

SELECT COUNT(eID) as count_pet
FROM pet;

count_pet3

It’s recommended that you pass in a primary key column or the * character to the COUNT function to count the number of rows in a table. As we’ve seen, both approaches will yield the same result.

The above syntax is the general SQL 2003 ANSI standard syntax. This helps to understand the way SQL COUNT() Function is used. But different database vendors may have different ways of applying COUNT() function.

Bellow, you can see that MySQL, PostgreSQL, and Microsoft SQL Server follows the same syntax as given above. But DB2 and Oracle differs slightly.

Overall, you can use * or ALL or DISTINCT or some expression along with COUNT to COUNT the number of rows w.r.t. some condition or all of the rows, depending up on the arguments you are using along with COUNT() function.

DBMS Support: COUNT() function

DBMSCommandMySQLSupportedPostgreSQLSupportedSQL ServerSupportedOracleSupported

DB2 and Oracle Syntax:

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

Parameters:

NameDescriptionALLApplies to all values. ALL returns the number of non NULL values.DISTINCTIgnored duplicate values and COUNT returns the number of unique nonnull values.expressionExpression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values. Expression of any type except text or image. Aggregate functions and subqueries are not permitted.*COUNTs all the rows in the target table whether or not they include NULLs.

Syntax diagram - COUNT() function

In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.

An important thing about COUNT() function:

When the * is used for COUNT(), all records ( rows ) are COUNTed if some content NULL but COUNT(column_name) does not COUNT a record if its field is NULL. See the following examples:

SQL COUNT rows in a table

In the following example, an asterisk character ( * ) is used followed by the SQL COUNT() which indicates all the rows of the table even if there is any NULL value.

Sample table: orders

To get number of rows in the 'orders' table, the following SQL statement can be used:

SQL Code:

SELECT COUNT(*)
FROM orders;

Relational Algebra Expression:

Relational Algebra Tree:

Output:

 COUNT(*)
---------
       34

Pictorial Presentation:

Select COUNT(*) from multiple tables

The following query COUNT the number of rows from two different tables (here we use employees and departments) using COUNT(*) command.

SQL Code:

SELECT(
      SELECT COUNT(*)
	  FROM   employees
	  ) AS Total_Employees,
	  (SELECT COUNT(*)
	  FROM   departments
	  ) AS No_Of_Departments
FROM dual

Output:

TOTAL_EMPLOYEES NO_OF_DEPARTMENTS
--------------- -----------------
            107                27

SQL COUNT( ) with column name

In this example SQL COUNT() function excludes the NULL values for a specific column if specified the column as an argument in the parenthesis of COUNT function.

Sample table: listofitem

To get the number of rows in the 'listofitem' table with the following condition -

1. COUNT number of rows for the column 'coname'

the following SQL statement can be used :

SQL Code:

SELECT COUNT(coname)      
FROM listofitem;

Relational Algebra Expression:

Relational Algebra Tree:

Output:

COUNT(CONAME)
-------------
            2

Explain:

The above statement COUNTs those rows for the 'coname' column which are not NULL.

SQL COUNT rows with user defined column heading

To get number of rows in the 'orders' table with the following condition -

1. result have to display with a heading 'Number of Rows',

the following SQL statement can be used:

SQL Code:

SELECT COUNT( *) as "Number of Rows"
FROM orders;

Output:

Number of Rows
--------------
            36

SQL COUNT( ) with where clause

The WHERE clause can be used along with SQL COUNT() function to select specific records from a table against a given condition.

Example:

Sample table: orders

To get number of rows in the 'orders' table with following condition -

1. ord_amount against the order is more than 1500,

the following SQL statement can be used :

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

0

Output:

COUNT ({*|[DISTINCT] expression}) OVER (window_clause)

1

Application of COUNT() function

In the subsequent pages, we have discussed how to apply COUNT() with various SQL clauses. For those applications, we have used Oracle 10g Express Edition.

COUNT with DISTINCT page discusses how to apply COUNT function with DISTINCT and also discusses how to apply COUNT function with ALL clause. Unlike using *, when ALL is used, NULL values are not selected.

COUNT HAVING page discusses how to apply COUNT function with HAVING clause and HAVING and GROUP BY .

COUNT with GROUP BY page discusses how to apply COUNT function with GROUP BY in ascending order and in descending order.

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition

Which function is using for counting the number of rows?

Which function is used to count the number of records in a column?

How can you count the total number of records of any table?