SQL COUNT Vs COUNT DISTINCT Function

SQL Server has two aggregate functions: Count and Count Distinct.

We cover many sorts of functions in aggregates, such as count ,max , avg, min, and sum.

The Count function, which is used to count the number of rows in a database table, is discussed in this article.

The SELECT query's DISTINCT keyword combined with the COUNT function reveals the number of unique data in the table's field.

SQL COUNT Function

COUNT() is a SQL function that returns the number of records in the output table. The SELECT statement in SQL is used with this function.

The COUNT function returns the total number of rows returned by the SELECT command, including NULL and duplicate entries.

When you combine a where condition with the COUNT function in a select statement, the COUNT() function is utilised to count the number of rows.

The number of rows from the table that satisfy the where condition will be returned. The COUNT() function will not count NULL values.

Example 1:

We've used the following two SQL examples in this article to demonstrate how to use the Count function in a query:

We have a table called Book with 5 columns in this example:

Let's say you want to count how many different bike colours there are in Book Table. You must construct the following SQL statement for this operation:

SELECT COUNT (Domain) AS Department FROM BookAdvance ;

Following the COUNT keyword, we must specify the column name as well as the name of the table on which the Count function will be run.

Output:

Because the Bike Color column does not contain any NULL values, the result of this query is six.

Example 2:

For the SQL COUNT function examples, we'll use the tables and data listed below.

Without any NULL or DISTINCT tests, we'll use COUNT to return the number of rows.

We discuss the Student table and count the number of values

SELECT COUNT(*) As Total 
FROM Student;

Output:

We'll just retrieve the number of rows returned by the select statement with the above query.

SQL COUNT DISTINCT Fuction

The SELECT query's DISTINCT keyword combined with the COUNT function reveals the number of unique data in the table's field.

We used the COUNT function with an expression in the previous examples.

We can also use the phrase in conjunction with the DISTINCT command to get all of the NON NULL values, which are also UNIQUE.

All duplicate values are removed before the function count is applied, or if you want to count the number of unique rows in the database, you can use the DISTINCT keyword.

Rows with null values for the column are not counted in this version of Count.

Example 1:

The execution of the Count Function with Distinct keyword is demonstrated in the following two SQL examples:

The Country table is created using the following query, which has four fields:

The following SQL statement from the Country dataset counts the unique values of the Continent column:

SELECT  COUNT (DISTINCT (Continent) )AS Landmass 
 FROM  Country ;

Output:

SQL Differnce Between COUNT And COUNT DISTINCT Function

There 5 contrast in count vs count distinct aggregate functions:-