Webinar Alert : Mastering  Manual and Automation Testing! - Reserve Your Free Seat Now

- SQL Server Blogs -

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. 

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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: Online SQL Queries for Practice Questions with Answers

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 Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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: Top 151+ SQL Interview Questions and Answers You Can't Miss

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: How to Ace Your Microsoft Power BI Certification Exam

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: How to Insert Multiple Rows Using Stored Procedure in SQL?

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: Power BI - Getting Started with Query Editor in Power BI

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: Data Anamolies-Understanding Them And Learning Ways To Resolve Them

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: How to Clear SQL Server Transaction Log File with DBCC Shrinkfile

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: SQL Developer Resume: Examples, Samples, & Tips (2024)

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: SQL Server Developer & Database Administrator Salary Structure

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

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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: All You Need to Know About SQL: SQL Tutorial for Beginners & Experienced

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    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.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

13 days 11 Oct 2024

QA Course

QA

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

Upcoming Class

7 days 05 Oct 2024

Salesforce Course

Salesforce

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

Upcoming Class

4 days 02 Oct 2024

Business Analyst Course

Business Analyst

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

Upcoming Class

6 days 04 Oct 2024

MS SQL Server Course

MS SQL Server

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

Upcoming Class

6 days 04 Oct 2024

Data Science Course

Data Science

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

Upcoming Class

13 days 11 Oct 2024

DevOps Course

DevOps

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

Upcoming Class

-0 day 28 Sep 2024

Hadoop Course

Hadoop

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

Upcoming Class

7 days 05 Oct 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

-0 day 28 Sep 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

14 days 12 Oct 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

6 days 04 Oct 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

7 days 05 Oct 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews