Summary  
This chapter explains how to use the SUM() aggregate function in SQL to calculate the total of values in a numeric column with optional filtering conditions.

General domain of usage  
Data analysis

`SUM()` returns the total of all the values in a given column. 

Here's an example:

SELECT SUM(population) 
FROM country 
WHERE continent='Asia';

The `SUM()` function calculates the total population of all Asian countries found in the `country` table.

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_sum_function_used(self):
        """Check that SUM() is used in the query."""
        with open("query.sql", "r") as file:
            query = file.read()
        condition = "sum" in query.lower()
        _dynamic_test(
            self,
            condition,
            "SUM() function is used in the query.",
            "SUM() function is not used in the query."
        )
    def test_correct_total_surfacearea(self):
        """Check that the result is the correct sum of surfacearea for Europe."""
        # Calculate expected sum based on init script
        europe_rows = [
            64589,    # Latvia
            41526,    # Netherlands
            91982,    # Portugal
            449964,   # Sweden
            603700,   # Ukraine
            323877,   # Norway
            1.50,     # Monaco
            316       # Malta
        ]
        # The database stores Monaco's surface area as 2 (not 1.5), so adjust expected sum accordingly
        expected_sum = sum([64589, 41526, 91982, 449964, 603700, 323877, 2, 316])

        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        result = self.cursor.fetchone()
        # result is tuple like (sum,)
        if result is not None:
            user_sum = float(result[0])
        else:
            user_sum = None
        # Allow small error due to float
        condition = user_sum is not None and abs(user_sum - expected_sum) < 0.01
        _dynamic_test(
            self,
            condition,
            f"Query returns the correct sum of surfacearea for Europe: {expected_sum}.",
            f"Query does not return the correct sum for Europe. Expected: {expected_sum}, Got: {user_sum}"
        )

    def test_no_extra_rows(self):
        """Check that only one row is returned."""
        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 a single row as expected.",
            f"Query returned {len(rows)} rows instead of 1."
        )

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):
    import re
    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 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 SUM() Function

解答