How to use SQL Count() Aggregate Function

Introduction

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. 

In this blog, we’ll learn the use of COUNT aggregate function in different aspects, that is,

• COUNT function with distinct
• COUNT function with HAVING clause
• COUNT function with GROUP BY clause
• using the COUNT function to count number rows
• difference between COUNT (*) and COUNT (1)

SQL Count function

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: MSBI Interview Questions & Answers for Fresher, Experienced

The result of the COUNT function depends on the argument that is passed  to it.

• By default, the COUNT function uses the ALL keywords whether you specify it or not. The ALL keywords mean that all items in the group are considered including the duplicate values. For example, if you have a group (1, 2, 3, 3, 4, 4) and apply the COUNT function, the result is 6.
• If the DISTINCT keyword is specified explicitly, then only unique non-null values are considered. The COUNT function returns four if you apply it to the group (1,2,3,3,4,4).
• The SQL Count function technically works in SQL Server versions 2008 and above, Azure SQL Data Warehouse, and Parallel Data Warehouse.

Syntax of Count function:

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;

SQL Parameters-

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 Count distinct

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. 

SQL Count with Group by

In some cases, you will be required to use the GROUP BY clause with the COUNT function.

Read: SQL Server Indexes-All You Need to Know

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. 

SQL Count with Having

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: SSIS Package - SSIS DB, Security and Upgrades

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;

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

Output:

COUNT(*)

----------

12

SQL Count with Having and Group by

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 -

1. number of agents for a particular 'commission',
2. number of agents for that particular 'commission' must be more than 3,

Read: SQL Server Analysis Services – All You Need to Know

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.

SQL Count rows

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;

Difference between Count(1) and Count(*)

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 Server Views - Everything You Should Know

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

Examples for SQL COUNT() Function

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

email

phone_number

hire_date

job_id

salary

manager_id

department_id

Example by using SQL COUNT (*) Function

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: Difference Between Clustered and Non-Clustered Index in the SQL

Result

COUNT (*)

5

SELECT
COUNT(*)
FROM
employees
WHERE
department_id = 6;

Result

COUNT (*)

5

Example by using SQL COUNT with GROUP BY clause

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: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

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

Example by using SQL COUNT (*) with ORDER BY clause

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

Example by using SQL COUNT with HAVING clause

To filter the groups by the result of the COUNT(*) function, we need to use the COUNT(*) function in the HAVING clause.

Read: What is Database? A Definitive Guide

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

Example by using SQL COUNT with (DISTINCT expression)

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: What Is The Difference Between The SQL Inner Join And Outer Joins?

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.

Conclusion

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: Difference Between Clustered and Non-Clustered Index in the SQL

FaceBook Twitter LinkedIn Pinterest Email