COUNT Function in SQL Server – Applications & Parameters

1. Objective

In our last SQL tutorial, we discussed SQL Cursor. Today, we will see COUNT Function in SQL. Moreover, in this SQL COUNT Function tutorial, we will discuss parameters and example. Also, we will see SQL COUNT Function applications. Along with this, we discuss Tables and diagrams in COUNT Function in SQL.

So, let us start COUNT Function in SQL Tutorial.

COUNT Function in SQL Server - Applications & Parameters

COUNT Function in SQL Server – Applications & Parameters

2. What is the COUNT Function in SQL?

The SQL COUNT function returns the number of columns in a table fulfilling the criteria indicated in the WHERE clause. It sets the quantity of lines or non-NULL column values.

Have a look at SQL Null Functions

COUNT() returns 0 if there were no coordinating columns.

The syntax for COUNT Function in SQL

COUNT(*)
COUNT( [ALL|DISTINCT] statement )

The above syntax is the general SQL 2003 ANSI standard linguistic syntax. This comprehends the manner in which SQL COUNT() Function is used. However, unique database sellers may have diverse methods for applying COUNT() function.

Generally, you can use * or ALL or DISTINCT or some statement alongside COUNT to COUNT the quantity of columns w.r.t. some condition or the majority of the lines, depend upon on the contentions you are utilizing alongside COUNT() function.

a. Definition and Usage of SQL Count Function

DBMS Support: COUNT function in SQL

DBMSCommand
MySQLSupported
PostgreSQLSupported
SQL ServerSupported
OracleSupported

DB2 and Oracle syntax:

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

Do you know about Primary and Foreign Key in SQL

b. Parameters in COUNT Function in SQL 

NameDescription
ALLThis applies to all values including number of non NULL values.
DISTINCTThis keyword ignores duplicate values and COUNT returns the number of unique nonnull values.
expressionThis expression is 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.
*COUNTs all the rows in the target table whether or not they include NULLs.
COUNT Function in SQL

COUNT Function in SQL – Parameters in SQL

We have talked about how to apply COUNT() with different SQL provisions. For those applications, we have utilized Oracle 10g Express Edition.
Something critical about COUNT Function in SQL:

At the point when the * is utilized for COUNT(), all records ( lines ) are COUNTed if some substance NULL, however, COUNT(column_name) does not COUNT a record if its field is NULL. See the accompanying cases:

Let’s revise SQL Auto Increment

In the accompanying case, a reference bullet character ( * ) is utilized trailed by the SQL COUNT() which demonstrates every one of the columns of the table regardless of whether there is any NULL esteem.
SQL Code:

SELECT COUNT(*)
FROM orders;
Copy

Output:
COUNT(*)
———
34
Pictorial Presentation of COUNT Function in SQL – 

COUNT Function in SQL

COUNT Function in SQL – Pictorial Presentation

c. SQL COUNT Function for Multiple Tables

COUNT rows in a table – 

Select COUNT(*) from multiple tables

Let’s have a look at SQL Check Constraint
SQL Code:

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

Output:
TOTAL_EMPLOYEES NO_OF_DEPARTMENTS
————— —————–
107       27
SQL Code:

SELECT COUNT(coname)
FROM listofitem;
Copy

Output:
COUNT(CONAME)
————-
2
Explanation – 
To get number of rows in the ‘orders’ table with the following condition –

  • The result has to display with a heading ‘Number of Rows’,

Do you know about SQL Operators
Following SQL statement can be used:

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

Output:
Number of Rows
————–
36

3. Application of COUNT Function in SQL

We have talked about how to apply COUNT() with different SQL conditions. For those applications, we have utilized Oracle 10g Express Edition.
COUNT with DISTINCT page talks about how to apply COUNT function with DISTINCT and furthermore examines how to apply COUNT function with ALL condition. Not at all like utilizing *, when ALL is utilized, NULL qualities are not chosen.

Have a look at SQL Distinct Keyword

COUNT HAVING page talks about how to apply COUNT function with HAVING statement and HAVING and GROUP BY.

COUNT with GROUP BY page talks about how to apply COUNT function with GROUP BY in rising request and in sliding request.

So, this was all about COUNT() Function in SQL

4. Conclusion – SQL COUNT Function

Hence, in this SQL COUNT Function tutorial, we learned the complete COUNT Function in SQL. Moreover, we saw its parameters, examples, code, and usage. Also, we discussed the applications of COUNT Function in SQL. Still, if you have any doubt, ask in the comment tab.
See also –
SQL NULL Values
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.