🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

[Copy and Paste Ready] Mastering SQL COUNT Function – From Basics to Advanced

When developing websites or applications, you’ll often find yourself needing to know things like “What is the total number of users?” or “How many different products are in a specific category?” In such cases, the SQL COUNT() function becomes extremely useful.

COUNT() is an aggregate function used to count the number of records (rows) in a table. At first glance, it may seem simple, but depending on how it's used, it can extract various kinds of information—making it a surprisingly powerful function. In this article, we’ll walk through everything from basic usage to practical applications, along with plenty of “copy-and-paste ready” code for beginners aspiring to become web creators.

The goal of this article is to help you experience the excitement of “SQL actually works when you write it!” Forget the complicated theory for now! First, copy the code, run it, and see the results for yourself to discover how fun SQL can be.

Let’s Warm Up! Prepare Sample Data

To try out SQL, you need a table with some data. For this tutorial, let’s create a users table that simulates a simple “user list.” The SQL below will create the table and insert some sample data.

All the code introduced in this article will work with this users table.

-- Delete the table if it exists (so you can try multiple times)
DROP TABLE IF EXISTS users;

-- Create users table
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  prefecture TEXT NOT NULL,
  email TEXT
);

-- Insert data
INSERT INTO users (id, name, prefecture, email) VALUES
(1, 'Taro Yamada', 'Tokyo', 'yamada@example.com'),
(2, 'Hanako Suzuki', 'Osaka', 'suzuki@example.com'),
(3, 'Jiro Sato', 'Tokyo', NULL),
(4, 'Sakura Ito', 'Fukuoka', 'ito@example.com'),
(5, 'Saburo Watanabe', 'Hokkaido', 'watanabe@example.com'),
(6, 'Shiro Takahashi', 'Tokyo', 'takahashi@example.com'),
(7, 'Misaki Tanaka', 'Osaka', NULL);

This table includes columns for ID, name, prefecture, and email. Note that the email fields for Sato (ID 3) and Tanaka (ID 7) are set to NULL (i.e., blank). This will be important later when we discuss how the COUNT() function behaves.

[Basic 1] Count All Records with `COUNT(*)`

Let’s start with the most basic usage: COUNT(*). The asterisk (*) means “all columns,” and COUNT(*) returns the total number of records in the table. Use this when you simply want to ask, “How many entries are in this table?”

Let’s count all users in the users table.

SELECT COUNT(*) FROM users;

Result:

7

Since we inserted data for 7 users earlier, the result is correctly returned as “7.” This is the foundation of using COUNT().

[Basic 2] Count Specific Columns with `COUNT(column_name)`

Next, let’s look at COUNT(column_name), which counts the number of records that have a value in the specified column.

This is important: COUNT(column_name) ignores NULL values. In other words, it does not count records where the column is empty.

Let’s count how many users in the users table have registered an email address.

SELECT COUNT(email) FROM users;

Result:

5

The result is “5.” That’s fewer than the 7 from COUNT(*), because the 2 users (Sato and Tanaka) who have NULL in the email column are excluded.

So remember: COUNT(*) gives the total number of rows, while COUNT(column_name) counts only the rows where that column has data.

[Advanced 1] Rename the Result Column with `AS`

By default, the result of COUNT() appears as something like COUNT(*) or COUNT(email), which isn’t very readable. This can be inconvenient when handling the result later.

In such cases, use AS to assign a more readable alias (name) to the result column.

SELECT COUNT(*) AS total_users FROM users;

Result:

total_users
-----------
7

By adding AS total_users, the result column is renamed to total_users. This makes it instantly clear that this value represents the total number of users. It also makes it easier to access the result in code, such as result['total_users'].

[Advanced 2] Count Unique Values with `COUNT(DISTINCT column_name)`

Next up is COUNT(DISTINCT), which is widely used in web analytics. The DISTINCT keyword means “remove duplicates,” allowing you to count the number of unique values.

For example, you might want to know “How many different prefectures are our users from?”

In the users table, 3 users are from Tokyo and 2 from Osaka. Let’s count how many unique prefectures there are.

SELECT COUNT(DISTINCT prefecture) AS unique_prefectures FROM users;

Result:

unique_prefectures
------------------
4

The result is “4.” The prefectures listed in the users table are Tokyo, Osaka, Fukuoka, and Hokkaido—four unique values.

This is useful in countless situations, like counting product categories in an e-commerce site or calculating unique visitors from access logs.

[Advanced 3] Count by Group with `GROUP BY`

COUNT() truly shines when used with the GROUP BY clause. This groups rows with the same value in a specified column and applies COUNT() to each group.

Want to know the number of users from each prefecture? This is exactly where GROUP BY comes in.

SELECT
  prefecture,
  COUNT(*) AS user_count
FROM
  users
GROUP BY
  prefecture;

Result:

prefecture | user_count
-----------|------------
Osaka      | 2
Hokkaido   | 1
Tokyo      | 3
Fukuoka    | 1

Perfect! We got a list of user counts by prefecture. The prefecture column is used to create groups, and COUNT(*) counts the number of rows in each group.

This is an essential technique for website analysis, such as figuring out which regions have the most users.

[Pro Tip] Filter Aggregated Results with `HAVING`

Sometimes you may want to filter results after aggregation with GROUP BY. For example, you might want to display only prefectures with two or more users.

Be careful: the WHERE clause filters individual records before aggregation, so you can’t use it to filter aggregated results like user_count.

To filter after aggregation, use the HAVING clause.

SELECT
  prefecture,
  COUNT(*) AS user_count
FROM
  users
GROUP BY
  prefecture
HAVING
  user_count >= 2;

Result:

prefecture | user_count
-----------|------------
Osaka      | 2
Tokyo      | 3

By adding HAVING user_count >= 2, only prefectures with two or more users—Osaka and Tokyo—are returned.

Remember: WHERE is for filtering individual records before aggregation, and HAVING is for filtering after GROUP BY.

[Interactive Corner] Try Running SQL in Your Browser!

Thank you for waiting! Here, we've prepared an environment where you can actually run the SQL you've learned so far with your own hands.

Copy the entire HTML code below, save it as a file named sql_test.html (or similar), and open it in your browser. The users table introduced in this article is already prepared, so try various SQL statements and enjoy seeing how the results change!

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL execution environment for COUNT()</title>
  <style>
    body { font-family: sans-serif; line-height: 1.6; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1 { color: #444; }
    textarea { width: 100%; height: 150px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; margin-bottom: 1rem; }
    button { background-color: #007bff; color: white; border: none; padding: 10px 20px; font-size: 16px; border-radius: 4px; cursor: pointer; }
    button:hover { background-color: #0056b3; }
    button:disabled { background-color: #ccc; cursor: not-allowed; }
    #result-container { margin-top: 2rem; border: 1px solid #ddd; padding: 1rem; border-radius: 4px; background: #f9f9f9; min-height: 50px; }
    #error-message { color: #d9534f; font-weight: bold; }
    table { border-collapse: collapse; width: 100%; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
    th { background-color: #f2f2f2; }
  </style>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
</head>
<body>

  <h1>Try SQL!</h1>
  <p>Enter SQL in the text area below and click the "Run" button. Try out various SQL examples introduced in this article!</p>

  <textarea id="sql-input">SELECT prefecture, COUNT(*) AS user_count
FROM users
GROUP BY prefecture
HAVING user_count >= 2;</textarea>
  
  <button id="execute-btn">Run</button>
  
  <div id="result-container">
    <p id="error-message"></p>
    <div id="result-output"></div>
  </div>

  <script>
    const sqlInput = document.getElementById('sql-input');
    const executeBtn = document.getElementById('execute-btn');
    const errorMsg = document.getElementById('error-message');
    const resultOutput = document.getElementById('result-output');

    let db;

    async function initDb() {
      executeBtn.disabled = true;
      executeBtn.textContent = 'Initializing...';
      try {
        const SQL = await initSqlJs({
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
        });
        db = new SQL.Database();
        
        // Setup initial table and data
        const setupSql = `
          DROP TABLE IF EXISTS users;
          CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            prefecture TEXT NOT NULL,
            email TEXT
          );
          INSERT INTO users (id, name, prefecture, email) VALUES
          (1, 'Taro Yamada', 'Tokyo', 'yamada@example.com'),
          (2, 'Hanako Suzuki', 'Osaka', 'suzuki@example.com'),
          (3, 'Jiro Sato', 'Tokyo', NULL),
          (4, 'Sakura Ito', 'Fukuoka', 'ito@example.com'),
          (5, 'Saburo Watanabe', 'Hokkaido', 'watanabe@example.com'),
          (6, 'Shiro Takahashi', 'Tokyo', 'takahashi@example.com'),
          (7, 'Misaki Tanaka', 'Osaka', NULL);
        `;
        db.run(setupSql);
        
        executeBtn.disabled = false;
        executeBtn.textContent = 'Run';
        resultOutput.innerHTML = '<p>Ready! Enter your SQL and click Run.</p>';

      } catch (err) {
        errorMsg.textContent = 'Failed to initialize database: ' + err.message;
        console.error(err);
      }
    }

    function executeSql() {
      if (!db) return;
      
      const sql = sqlInput.value;
      errorMsg.textContent = '';
      resultOutput.innerHTML = '';

      try {
        const results = db.exec(sql);
        if (results.length === 0) {
          resultOutput.innerHTML = '<p>The query succeeded but returned no result set (e.g., INSERT, UPDATE etc.)</p>';
          return;
        }
        
        results.forEach(result => {
          const table = document.createElement('table');
          const thead = document.createElement('thead');
          const tbody = document.createElement('tbody');
          
          const headerRow = document.createElement('tr');
          result.columns.forEach(colName => {
            const th = document.createElement('th');
            th.textContent = colName;
            headerRow.appendChild(th);
          });
          thead.appendChild(headerRow);
          
          result.values.forEach(row => {
            const bodyRow = document.createElement('tr');
            row.forEach(cellValue => {
              const td = document.createElement('td');
              td.textContent = cellValue === null ? 'NULL' : cellValue;
              bodyRow.appendChild(td);
            });
            tbody.appendChild(bodyRow);
          });
          
          table.appendChild(thead);
          table.appendChild(tbody);
          resultOutput.appendChild(table);
        });

      } catch (err) {
        errorMsg.textContent = 'SQL Error: ' + err.message;
        console.error(err);
      }
    }

    executeBtn.addEventListener('click', executeSql);
    
    initDb();
  </script>
</body>
</html>

[Give It a Try!]

Things to Watch Out For & Trivia

COUNT(*) vs COUNT(1) vs COUNT(column_name)

You may see the syntax COUNT(1). This means assigning the constant “1” to each row and counting it. In most databases, there’s no difference in behavior or performance between COUNT(*) and COUNT(1); both count all rows. On the other hand, COUNT(column_name) does not count NULL values, as explained earlier, so the purpose is different. When in doubt, use COUNT(*) when you want to count all rows, or COUNT(column_name) when you want to count only non-NULLs.

Performance

Running COUNT(*) on a huge table with millions or tens of millions of rows can take considerable time, especially when you’re counting all rows without any condition, as the entire table needs to be scanned. If you frequently need the total count, consider alternatives like summary tables—but for now, knowing the basic usage is sufficient.

Related Functions: Other Aggregates

Once you’ve learned COUNT(), it becomes easy to pick up other aggregate functions. These become even more powerful when used with GROUP BY.

For example, to find the highest user ID, use the following:

SELECT MAX(id) AS latest_user_id FROM users;

Conclusion

In this article, we've covered the COUNT() function for counting records—from basic usage to advanced techniques—in a brisk overview.

COUNT() is a fundamental step in data analysis. Try running the code in this article several times to get a feel for “counting data.” Once you're comfortable with SQL, your ability to analyze and interpret data will expand dramatically. Keep learning, and enjoy the journey!

Related Templates