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,
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.
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.
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.
This is the column or expression whose non-null values will be counted.
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
Optional. These are conditions that must be met for the records to be selected.
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.
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.
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.
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
To get data of 'commission' and number of agents for that commission from the 'agents' table with the following conditions -
the following SQL statement can be used:
SELECT commission, COUNT (*) FROM agents GROUP BY commission HAVING COUNT (*)>3;
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.
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.
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
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
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.
SELECT COUNT(*) FROM employees;
To find how many employees who work in the department_id 6, you add the WHERE clause to the query as follows:
SELECT COUNT(*) FROM employees WHERE department_id = 6;
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
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;
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;
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
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;
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)|
You can use the COUNT DISTINCT to get the number of managers as the following query:
SELECT COUNT(DISTINCT manager_id) FROM employees;
|COUNT (DISTINT manager_id)|
Note that the president does not have the manager.
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!
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.
Receive Latest Materials and Offers on SQL Server Course