SQL COUNT Function using SELECT command

In SQL, the COUNT function is a powerful aggregate function used to count the number of rows in a result set or the number of occurrences of a specific value in a column.

Basic Syntax of SQL SELECT COUNT

SELECT COUNT(*) FROM TABLE;

Now let’s understand with different examples:

Database Structure:

Table: users

user_id	username	age
1	John	25
2	Jane	22
3	Bob	30
4	Alice	28

Table: orders

order_id	user_id	product_id
101	1	1
102	2	2
103	1	3
104	3	1
105	4	2

Example 1: Counting all records in a table

SELECT COUNT(*) FROM users;

COUNT(*)
4

Example 2: Counting users older than 18.

SELECT COUNT(*) FROM users WHERE age > 18;

COUNT(*)
3

Example 3: Counting orders for each product.

SELECT product_id, COUNT(*) FROM orders GROUP BY product_id;

product_id	COUNT(*)
1	2
2	2
3	1

Example 4: Counting users with a specified attribute, handling NULLs.

SELECT COUNT(age) FROM users;

COUNT
4

Example 5: Misusing the COUNT function in a subquery.

SELECT username, (SELECT COUNT(*) FROM users) as total_count FROM users;

username	total_count
John	4
Jane	4
Bob	4
Alice	4

Example 6: Counting NULL Values

-- Counting NULL values in the "age" column of the "users" table
SELECT COUNT(*) FROM users WHERE age IS NULL;

Output:

Count
0

---