15
NovDiwali Deal : Flat 20% off + 2 free self-paced courses + Free Ebook + $200 Voucher - SCHEDULE CALL
The SQL COUNT function or simply COUNT() is an aggregate function that returns the number of rows returned by a query. We can use this aggregate function in the SELECT statement to get a particular number of employees, the number of employees in each department, the number of employees who hold a specific job, etc. Basically, we use the count function to get the number of records required. Now, let’s understand more about SQL for Count function.
Learn SQL Server in the Easiest Way
In this blog, we’ll learn the use of COUNT aggregate function in different aspects, that is,
The Count function works with the collaboration of the SELECT function. Primarily, the COUNT() function returns the number of records returned by a SELECT query. In this function, NULL values are not counted at all or in technical terms; COUNT function only includes NOT NULL values.
Read: Database Filters-Getting pure data from a pool of data
The result of the COUNT function depends on the argument that is passed to it.
SELECT COUNT (expression)
FROM table_name
WHERE condition(s);
Where, expression parameter may have a field or a string value. And this is a mandatory field.
Or
The syntax for the COUNT function when grouping the results by one or more column is:
SELECT expression1, expression2,……,expression_n;
COUNT (aggregate_expression)
FROM table_name
WHERE condition(s);
GROUP BY expression1, expression2,…..,expression_n;
expression1, expression2, ... expression_n
Expressions that are not encapsulated within the COUNT function and must be included in the GROUP BY clause at the end of the SQL statement.
aggregate_expression
This is the column or expression whose non-null values will be counted.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. These are conditions that must be met for the records to be selected.
Sign up for a demo SQL class if you want to learn and gain in-depth SQL knowledge.
SQL Server Training & Certification
You can use the DISTINCT clause within the COUNT function.
For example, the SQL statement below returns the number of unique departments where at least one employee has a first_name of 'John.
'
SELECT COUNT (DISTINCT DEPARTMENT) AS “Unique departments.”
FROM employees
WHERE first_name = ‘John’;
Again, the COUNT (DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.
In some cases, you will be required to use the GROUP BY clause with the COUNT function.
Read: Excel with SQL- 7 Best SQL Books You Must Read
For example, you could also use the COUNT function to return the name of the department and the number of employees (in the associated department) that are in the state of 'CA.'
SELECT department, COUNT (*) AS “Number of employees.”
FROM employees
WHERE state = ‘LA’;
GROUP BY department;
Because we have listed one column in the SELECT statement that is not encapsulated in the COUNT function, we must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.
The HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT() function.
Read: 70+ Most Asked SSIS Interview Questions for Freshers & Experienced
The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.
Example:
To get data of the number of agents from the 'agents' table with the following condition -
1 number of agents must be greater than 3, the following SQL statement can be used:
SELECT COUNT (*)
FROM agents
HAVING COUNT (*)>3;
AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO |
A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 |
A003 | Alex | London | 0.13 | 078-22255588 |
A003 | Alford | New York | 0.12 | 008-22544166 |
A011 | Ravi Kumar | Bangalore | 0.15 | 008-22536178 |
A010 | Saty kumar | Chennai | 0.14 | 078-22255588 |
A012 | Lucida | San Jose | 0.12 | 008-22544166 |
A005 | Anderson | Brisbane | 0.13 | 008-22536178 |
A001 | Subbarao | Bangalore | 0.14 | 078-22255588 |
A002 | Mukesh | Mumbai | 0.11 | 008-22544166 |
A006 | McDen | London | 0.15 | 008-22536178 |
A004 | Ivan | Toronto | 0.15 | 078-22255588 |
A009 | Benjamin | Hampshair | 0.11 | 008-22536178 |
Output:
COUNT(*)
----------
12
Sample table: agents
AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO |
A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 |
A003 | Alex | London | 0.13 | 078-22255588 |
A003 | Alford | New York | 0.12 | 008-22544166 |
A011 | Ravi Kumar | Bangalore | 0.15 | 008-22536178 |
A010 | Saty kumar | Chennai | 0.14 | 078-22255588 |
A012 | Lucida | San Jose | 0.12 | 008-22544166 |
A005 | Anderson | Brisbane | 0.13 | 008-22536178 |
A001 | Subbarao | Bangalore | 0.14 | 078-22255588 |
A002 | Mukesh | Mumbai | 0.11 | 008-22544166 |
A006 | McDen | London | 0.15 | 008-22536178 |
A004 | Ivan | Toronto | 0.15 | 078-22255588 |
A009 | Benjamin | Hampshair | 0.11 | 008-22536178 |
To get data of 'commission' and number of agents for that commission from the 'agents' table with the following conditions -
Read: How to Increase the Speed of SQL Query Execution
the following SQL statement can be used:
SELECT commission, COUNT (*)
FROM agents
GROUP BY commission
HAVING COUNT (*)>3;
COMMISSION | COUNT(*) |
.15 | 4 |
Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
The COUNT() function returns the number of rows that matches specified criteria.
Syntax
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
SELECT COUNT (column_name) FROM table_name;
SELECT COUNT (1) FROM temp;
SELECT COUNT (*) FROM temp;
COUNT (1)
means it will return a single value among the total number of records.
COUNT (*)
means it will return all values among the total number of records.
Performance-wise you can differentiate that COUNT (1)
function process is a little bit slow as compared to COUNT (*)
function.
Inside large queries, it is always better to use COUNT (1)
function rather than using COUNT (*)
. If you use COUNT (column), the database must actually inspect the individual values in the column, because it will not count NULLs. Aggregate functions like COUNT and SUM always ignore NULLs.
Read: SQL REPLACE() Function: A Step-By-Step Guide
It is better to understand that the database optimizer is smart enough to realize that the literal value one will never be NULL, and is needed to be inspected in every row. It is also not surprising to see that two separate logic modules handle the actions of appending the 1s into every row produced by the FROM clause and counting the 1s, and maybe the module doing the counting doesn't know where the 1s came from. So, just use COUNT (*).
The count will give you non-null record number of given fields. Say you have a table named TEMP
SELECT 1 FROM temp
SELECT 0 FROM temp
SELECT * FROM temp
will all return the same number of records, that is the number of rows in table A. Still the output is different. If there are 3 records in the table. With X and Y as field names.
SELECT 1 FROM temp will give you
1
1
1
SELECT 0 FROM temp will give you
0
0
0
SELECT * FROM temp will give you (assume two columns X and Y is in the table)
X Y
-- --
value1 value1
value2 (null)
value3 (null)
So, all three queries return the same number. Unless you use
SELECT count(Y) FROM temp
Since there is only one non-null value you will get 1 as output.
The COUNT (*)
function counts the number of rows produced by the query, whereas COUNT (1)
counts the number of 1 value. Note, that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause. This also applies to literals in aggregate functions, such as COUNT (1)
.
In simple terms,
COUNT(*) --> This will give you the complete count including the NULLS
COUNT(1) --> This will give you the complete count without the NULLS
COUNT(*) is mentioned specifically in ANSI.
OUNT(1) has been optimized out by RDBMS vendors because of this superstition. Otherwise it would be evaluated as per ANSI.
COUNT(*)
It counts total number of rows in a table
COUNT(1) is nothing but COUNT(column)
It counts without null values in the particular column
For example
CREATE TABLE TEMP (CITY VARCHAR(20),STATE VARCHAR(20))
INSERT INTO TEMP (CITY,STATE) VALUES('CHENNAI','TN')
INSERT INTO TEMP (CITY,STATE) VALUES('HYDERABAD','AP')
INSERT INTO TEMP (CITY,STATE) VALUES('BANGALORE','KR')
INSERT INTO TEMP (CITY,STATE) VALUES('COCHIN','KE')
INSERT INTO TEMP (CITY,STATE) VALUES(NULL,'KE')
INSERT INTO TEMP (CITY,STATE) VALUES(NULL,'KE')
INSERT INTO TEMP (CITY,STATE) VALUES('DELHI',NULL)
SELECT COUNT(*) AS CountOfAll FROM TEMP
CountOfAll
-----------
7
SELECT COUNT(CITY) AS CountOfCity FROM TEMP
CountOfCity
-----------
5
SELECT COUNT(STATE) AS CountOfState FROM TEMP
CountOfState
------------
6
Let’s take examples to see how the COUNT() function works. We will use the employees table in the sample database for the demonstration purposes.
Employees |
*employee_id |
first_name |
last_name |
phone_number |
hire_date |
job_id |
salary |
manager_id |
department_id |
SELECT
COUNT(*)
FROM
employees;
Result |
COUNT (*) |
40 |
To find how many employees who work in the department_id 6, you add the WHERE clause to the query as follows:
Read: All you Need to Know About SQL Database Administrator Salary
Result |
COUNT (*) |
5 |
SELECT
COUNT(*)
FROM
employees
WHERE
department_id = 6;
Result |
COUNT (*) |
5 |
SELECT
department_id,
COUNT(*)
FROM
employees
GROUP BY
department_id;
Output
department_id | COUNT(*) |
1 | 1 |
2 | 2 |
3 | 6 |
4 | 1 |
5 | 7 |
6 | 5 |
7 | 1 |
8 | 6 |
9 | 3 |
10 | 6 |
11 | 2 |
To get the department name in the result set, we need to use the inner join to join the employee's table with the department's table as follows:
Read: Power BI - Getting Started with Query Editor in Power BI
SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id;
Output
department_id | department_name | COUNT(*) |
1 | Administration | 1 |
2 | Marketing | 2 |
3 | Purchasing | 6 |
4 | Human Resources | 1 |
5 | Shipping | 7 |
6 | IT | 5 |
7 | Public Relations | 1 |
8 | Sales | 6 |
9 | Executive | 3 |
10 | Finance | 6 |
11 | Accounting |
You can use the COUNT(*) function in the ORDER BY clause to sort the number of rows per group. For example, the following statement gets the number of employees for each department and sorts the result set based on the number of employees in descending order.
SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
ORDER BY
COUNT(*) DESC;
Output
department_id | department_name | COUNT(*) |
5 | Shipping | 7 |
3 | Purchasing | 6 |
8 | Sales | 6 |
10 | Finance | 6 |
6 | IT | 5 |
9 | Executive | 3 |
2 | Marketing | 2 |
11 | Accounting | 2 |
1 | Administration | 1 |
4 | Human Resources | 1 |
7 | Public Relations |
To filter the groups by the result of the COUNT(*) function, we need to use the COUNT(*) function in the HAVING clause.
Read: A Comprehensive NoSQL Tutorial Guide for Beginner: Learn Step-by-Step
For example, the following statement gets the departments and their number of employees. Also, it selects only departments whose number of employees is greater than 5.
SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
COUNT(*) > 5
Output
department_id | department_name | COUNT(*) |
5 | Shipping | 7 |
3 | Purchasing | 6 |
8 | Sales | 6 |
10 | Finance | 6 |
To get the number of jobs in the employee's table, you apply the COUNT function to the job_id column like the following statement:
SELECT
COUNT(job_id)
FROM
employees;
Result |
COUNT (job_id) |
40 |
The query returns 40 that includes the duplicate job id. We expected to find the number of jobs that are holding by employees.
Read: Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples
To remove the duplicate, we add the DISTINCT keyword to the COUNT function as follows:
COUNT (DISTINCT job_id) |
19 |
You can use the COUNT DISTINCT to get the number of managers as the following query:
SELECT
COUNT(DISTINCT manager_id)
FROM
employees;
Result |
COUNT (DISTINT manager_id) |
10 |
Note that the president does not have the manager.
SQL Server Training & Certification
In the above write up, we have learned how we can perform multiple methods using sql COUNT aggregate functions. The function, when applied with proper syntax, will return the number of rows in a group. Using raw COUNT() function has a different application as with the use of several clauses like HAVING and GROUP BY. Also, the SQL Server queries are case insensitive. So, it does not make any difference if you give JOHN, John, or john in WHERE condition. Send us a query if you have any doubts, and keep practicing the SQL queries! Happy querying!
Now, that you know so much how to use SQL for count function, you might as well go ahead and learn SQL properly. Sign up for an online JanBask Training Course right now!
Read: What is the Substring Function in the SQL? Example of SQL Server SubstringFaceBook Twitter LinkedIn Pinterest Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews