Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

How to use SQL Count() Aggregate Function

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.

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.

The result of the COUNT function depends on the argument that is pass 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.

SQL Server Curriculum

SQL Count with 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: Different Type of SQL Joins

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.

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,

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 Server quiz

SQL Count rows

The COUNT() function returns the number of rows that matches specified criteria.

Read: How to Download and Install Microsoft SQL Server Data Tools?

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.

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

Read: What Is SQL Candidate Key? Difference between Primary Key & Candidate Key

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
free SQL Server demo

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:

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:


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 2

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 1

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.

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.

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 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!


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

-1 day 19 Nov 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

-1 day 19 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews