Summary  
This chapter covers the SQL COUNT() aggregation function for counting all rows in a table or only those that meet specified criteria.

General domain of usage  
Data analysis

`COUNT()` is a function that allows you to count rows in a table or count how many rows match certain criteria. 

Let's see an example:

To count the total number of rows in the table, we can use the following syntax:

import unittest
import psycopg2
import pandas as pd

class TestPostgresQuery(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_count_asian_countries_population_over_1m(self):
        """Check if the query returns the correct count for Asian countries with population > 1,000,000."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        # There are 3 countries in Asia with population > 1,000,000: Japan, Thailand, Philippines
        expected_count = 3
        # The result should be a single row with a single value
        condition = (
            len(rows) == 1 and
            len(rows[0]) == 1 and
            rows[0][0] == expected_count
        )
        _dynamic_test(
            self,
            condition,
            "Correct count returned for Asian countries with population over 1,000,000.",
            f"Expected count {expected_count}, but got {rows[0][0] if rows and rows[0] else 'no result'}."
        )

    def test_no_extra_rows(self):
        """Ensure only a single row is returned by the query."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        condition = len(rows) == 1
        _dynamic_test(
            self,
            condition,
            "Query returns exactly one row.",
            f"Query returned {len(rows)} rows instead of 1."
        )

    def test_no_extra_columns(self):
        """Ensure only a single column is returned by the query."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        condition = len(rows) > 0 and len(rows[0]) == 1
        _dynamic_test(
            self,
            condition,
            "Query returns exactly one column.",
            f"Query returned {len(rows[0]) if rows else 0} columns instead of 1."
        )

    def test_query_is_case_sensitive_for_asia(self):
        """Ensure the query does not match 'asia' in lowercase: only 'Asia' should be matched."""
        # Insert a country with continent as 'asia' (lowercase) and population > 1M
        self.cursor.execute("INSERT INTO country (id, name, continent, region, surfacearea, capital, population) VALUES (100, 'FakeAsia', 'asia', NULL, 1000, 'FakeCity', 2000000);")
        self.connection.commit()
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        expected_count = 3  # Should NOT count the lowercase 'asia' row
        condition = (
            len(rows) == 1 and
            len(rows[0]) == 1 and
            rows[0][0] == expected_count
        )
        _dynamic_test(
            self,
            condition,
            "Query is case sensitive for 'Asia'.",
            f"Query counted rows with 'asia' (lowercase). Expected {expected_count}, got {rows[0][0] if rows and rows[0] else 'no result'}."
        )
        # Clean up
        self.cursor.execute("DELETE FROM country WHERE id=100;")
        self.connection.commit()

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\\s{2,}", " ", text)
    text = re.sub(r"\\s*([,:?])\\s*", r"\\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

This module is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications.

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

The COUNT() Function

Solution