02.4 DISTINCT and calculated columns

01 | FROM STORED VALUES TO USEFUL OUTPUT

The database stores each club’s category, but an organiser may want a list of category choices without repetition. It stores fees in cents, but a report may need a human-readable amount.

DISTINCT controls duplicate result rows. Expressions calculate values for each result row. Neither needs to change the original table.

Use this section’s expanded Clubs table. It is a separate retrieval dataset , not a migration of the Section 01 database. All examples use SQLite. The queries below read data without changing stored records.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    room TEXT,
    fee_cents INTEGER NOT NULL,
    session_minutes INTEGER NOT NULL
);

INSERT INTO Clubs
    (club_id, club_name, category, capacity, room, fee_cents, session_minutes)
VALUES
    (1, 'Robotics', 'Technology', 12, 'AIR Lab', 1500, 90),
    (2, 'Game Design', 'Technology', 16, 'Room 12', 0, 60),
    (3, 'Art', 'Creative', 12, 'Studio', 800, 60),
    (4, 'Film', 'Creative', 20, NULL, 1000, 90),
    (5, 'Chess', 'Strategy', 16, 'Room 12', 0, 45),
    (6, 'Coding', 'Technology', 20, 'AIR Lab', 0, 60),
    (7, 'Drama', 'Creative', 24, NULL, 1200, 90),
    (8, 'Robotics Junior', 'Technology', 10, '', 500, 45);
"""

query = """
SELECT club_id, club_name, category, capacity, room, fee_cents, session_minutes
FROM Clubs
ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | SHARED REFERENCE DATA

Money is stored as integer cents to avoid using binary floating point as the stored monetary amount. fee_cents = 0 means free, not unknown. Film and Drama have NULL rooms; Robotics Junior has an empty text room, deliberately included so you can compare the two situations.

03 | DISTINCT REMOVES DUPLICATE RESULT ROWS

SELECT DISTINCT category
FROM Clubs
ORDER BY category;

Technology appears in several source records but only once in this result. DISTINCT does not delete duplicate source records; it removes duplicate combinations from the selected output.

Order is still a separate concern. DISTINCT alone does not guarantee an alphabetical result.

04 | TWO COLUMNS MEAN DISTINCT PAIRS

SELECT DISTINCT category, capacity
FROM Clubs
ORDER BY category, capacity;

DISTINCT applies to the complete selected combination. Technology can appear several times when paired with different capacities.

Adding a unique club_id would make every selected row distinct, so it would not produce one row per category. Choose the columns that match the question.

05 | AS NAMES A RESULT COLUMN

SELECT club_name AS name, capacity AS maximum_members
FROM Clubs
ORDER BY club_id;

AS supplies an alias, a heading used in the query result. It does not rename the stored database column.

Clear aliases help when an expression would otherwise produce a long heading. Simple names with underscores are easy to reuse in ORDER BY.

06 | CALCULATE A PER-ROW VALUE

SELECT club_name, session_minutes,
       session_minutes * 4 AS four_session_minutes
FROM Clubs
ORDER BY club_id;

For each source row, multiply the duration by four. A 90-minute session gives 360 minutes across four equal sessions.

This is a row calculation, not a total across all clubs. Aggregates such as SUM are taught in the next section. No new stored column has been created.

07 | DIVISION IN SQLITE NEEDS CARE

SELECT club_name, fee_cents / 100 AS truncated_amount,
       fee_cents / 100.0 AS amount
FROM Clubs
ORDER BY club_id;

SQLite uses integer division when both operands are integers, truncating towards zero. Our sample fees happen to be multiples of 100, which can hide that problem.

SELECT 1550 / 100 AS integer_result,
       1550 / 100.0 AS decimal_result;

Using 100.0 forces real-number arithmetic here: 15.5 rather than 15. Monetary values remain stored as integer cents; a display calculation is not a reason to store exact money as binary floating point.

08 | PARENTHESES AND MEANING

SELECT club_name,
       (fee_cents + 200) / 100.0 AS amount_with_surcharge
FROM Clubs
ORDER BY club_id;

Add a fictional 200-cent surcharge before converting to units. Without the parentheses, division would apply to 200 first and produce a different calculation.

Use units to check reasoning: fee_cents + 200 is still cents, then dividing by 100 converts the display amount. Do not combine minutes and cents just because both are numeric.

09 | CONCATENATE TEXT

SELECT club_name || ' (' || category || ')' AS club_label
FROM Clubs
ORDER BY club_id;

In SQLite, || concatenates text. The spaces and parentheses inside the quoted strings become part of the output.

Do not assume + concatenates SQL text; in SQLite it is an arithmetic operator. String-concatenation syntax varies by database product.

10 | HANDLE MISSING VALUES DELIBERATELY

SELECT club_name,
       COALESCE(room, 'To be confirmed') AS room_display
FROM Clubs
ORDER BY club_id;

COALESCE returns the first non-NULL argument. It substitutes a display label for Film and Drama, but leaves Robotics Junior’s empty string unchanged.

Arithmetic or text concatenation involving NULL usually produces NULL. For example, a club name concatenated with an unknown room produces NULL unless you deliberately supply a replacement. Do not silently turn unknown numeric values into zero unless that matches their meaning.

11 | FILTER SOURCE FACTS; SORT THE RESULT

SELECT club_name, fee_cents / 100.0 AS amount
FROM Clubs
WHERE fee_cents >= 500
ORDER BY amount DESC, club_id ASC;

Use source columns in WHERE for clear, portable filtering. Use the alias in ORDER BY to sort the displayed calculation. SQLite supports some extra alias behaviour, but relying on it can make queries less portable.

SELECT DISTINCT room
FROM Clubs
WHERE room IS NOT NULL
ORDER BY room;

12 | REPORT CHALLENGE

SELECT club_name, fee_cents / 100.0 AS amount,
       session_minutes * 4 AS four_session_minutes
FROM Clubs
WHERE category = 'Technology'
ORDER BY amount DESC, club_id ASC;

You are ready for the section challenges. Later, joins and aggregates will let us combine related tables and summarise several records at once.

KEEP LEARNING

Previous page 02 Retrieving data: challenges

Reference: SQLite SELECT · SQLite expressions and filters