SQL Count function with example – Count aggregate function returns total number of rows based on some condition like group by, distinct condition and WHERE condition etc.
POINTS:
- Count function returns the total number of rows in the specified column / field.
- It works on both numeric and non numeric data types.
COUNT function syntax
Select Count ([(DISTINCT | ALL EXPRESSION)]) FROM [TABLE_NAME]
DISTINCT is optional.
SQL COUNT(*)
SQL Count(*) is a special function that returns the count of all the rows (including null values and duplicates) in a specified table.
Example:
This is an example query to get the number of books in the BOOKS table. It can count the rows that have null values and also duplicates.
Query example:
Select Count(*) from books;
SQL Distinct Count
If want to count distinct / Unique rows in a table , then you need to use DISTINCT keyword in query. Using distinct null values are not allowed to count.
Query example:
Select count(DISTINCT isbn_no) from books WHERE price=100;
SQL Count with duplicate but not NULL
Below query example, give count with duplicates but they will not allow with null values.
Get the number of books with price 100 in the books table.
Query example:
Select count(isbn_no) from books WHERE price=100;