SQL COUNT Tutorial 🎯

Welcome to our SQL COUNT tutorial! In this lesson, we'll dive deep into the COUNT function, a powerful tool in SQL for counting rows, distinct values, and more. Let's get started! 📝

What is SQL COUNT?

The SQL COUNT function is used to count the number of rows or occurrences of specific values in a table. It's a fundamental function for querying databases and understanding the data structure. 💡

sql

SELECT COUNT(*) FROM table_name;

In the above example, COUNT(*) counts the total number of rows in the table_name.

Counting Rows 📝

Let's use a simple example to understand how to count rows with COUNT(*). We have a users table with the following data:

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'johndoe@example.com'),
       (2, 'Jane Smith', 'janesmith@example.com'),
       (3, 'Mike Johnson', 'mikejohnson@example.com');

To count the total number of rows in the users table, we use:

sql

SELECT COUNT(*) FROM users;

Result:

COUNT(*)
--------
3

Counting Distinct Values 📝

When you want to count unique values in a column, use the COUNT(DISTINCT column_name) syntax. Let's count the number of unique users in the users table based on their names:

sql

SELECT COUNT(DISTINCT name) FROM users;

Result:

COUNT(DISTINCT name)
---------------------
3

Counting Non-Null Values 📝

In some cases, you might want to count only the non-null values in a column. The COUNT(column_name) syntax will work for this. However, it will count null values as well. If you want to exclude null values, use the COUNT(DISTINCT column_name) syntax:

sql

SELECT COUNT(name), COUNT(DISTINCT name) FROM users;

Result:

COUNT(name)  | COUNT(DISTINCT name)
------------- | ----------------------
3            | 2

In the example above, the COUNT(name) counts all rows (including null values), while the COUNT(DISTINCT name) counts only the non-null values.

Advanced Example 💡

Let's count the number of unique users who have signed up in 2022:

sql

SELECT COUNT(DISTINCT name)
FROM users
WHERE YEAR(email) = YEAR(CURDATE());

Result:

COUNT(DISTINCT name)
---------------------
1

Quiz 📝

Wrapping Up ✅

In this tutorial, we learned about the SQL COUNT function, which helps count rows, distinct values, and more. We practiced using COUNT(*), COUNT(DISTINCT column_name), and understood the difference between counting all values and counting only non-null values.

Now that you have a grasp of counting rows, it's time to explore more SQL functions and apply them to your projects. Happy coding! 🚀