Using COUNT with Aggregate in Postgres

Introduction

PostgreSQL is a powerful and feature-rich database management system. One of its strengths lies in its ability to perform complex queries, including aggregations. In this article, we’ll explore how to use the COUNT function with aggregate operations in PostgreSQL.

Understanding COUNT

The COUNT function returns the number of rows that match a specific condition. However, when used alone, it only provides a simple count of records without any additional context. To get around this limitation, we can use aggregate functions like SUM, AVG, and MAX in combination with COUNT.

Aggregating Multiple Columns

Let’s assume we have two tables: event and ticket. The event table contains information about events, while the ticket table stores details about individual tickets. We want to perform an aggregation on both tables using the COUNT function.

Table Structure

Here are the table structures for event and ticket:

CREATE TABLE event (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    category_id INTEGER,
    status VARCHAR(10)
);

CREATE TABLE ticket (
    id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books(id),
    order_id INTEGER REFERENCES purchases(id),
    show_id INTEGER REFERENCES shows(id),
    showtime VARCHAR(10) NOT NULL
);

Relationships Between Tables

The event table has a foreign key constraint referencing the id column in the category table. Similarly, the ticket table has a foreign key constraint referencing the book_id column in the books table.

Example Query

Suppose we want to retrieve event-level aggregate data from both tables, including a new sales column that contains the count of tickets sold for each event. Here’s an example query:

SELECT 
    e.id AS id,
    e.name AS name,
    e.description AS description,
    c.slug AS category,
    COUNT(t.id) AS sold,
    json_agg(json_build_object('id', b.id, 'title', b.title, 'description', b.description, 'price', b.price, 'available', b.qty_available, 'qty_per_sale', b.qty_per_sale, 'sales', ts.ticket_count))::JSONB AS book,
    json_agg(json_build_object('id', s.id, 'startDate', s.start_date, 'endDate', s.end_date, 'daysAhead', (s.start_date::DATE - NOW()::DATE), 'times', s.times))::JSONB as dates
FROM event e 
LEFT JOIN books b ON b.event_id = e.id 
LEFT JOIN shows s ON s.event_id = e.id 
LEFT JOIN category c ON e.category_id = c.id 
LEFT JOIN ticket t ON t.book_id = b.id 
LEFT JOIN (
    SELECT book_id, COUNT(1) AS ticket_count
    FROM ticket
    GROUP BY book_id
) ts ON ts.book_id = b.id
WHERE (status = 'PUBLISHED' OR status = 'PROMOTED')
AND s.end_date >= DATE(NOW())
AND e.is_private = FALSE
AND s.id = t.show_id
AND t.canceled = FALSE
GROUP BY e.id, c.slug
ORDER BY sold
LIMIT 30;

This query uses a subquery to calculate the ticket count for each book and joins it with the original query. The result includes a new sales column that contains the count of tickets sold for each event.

Solution Using Common Table Expressions (CTEs)

Another approach is to use a common table expression (CTE) to simplify the query. Here’s an updated example:

WITH ticket_summary AS (
    SELECT book_id, COUNT(1) AS ticket_count
    FROM ticket
    GROUP BY book_id
),
event_data AS (
    SELECT 
        e.id AS id,
        e.name AS name,
        e.description AS description,
        c.slug AS category,
        COUNT(t.id) AS sold,
        json_agg(json_build_object('id', b.id, 'title', b.title, 'description', b.description, 'price', b.price, 'available', b.qty_available, 'qty_per_sale', b.qty_per_sale))::JSONB AS book,
        json_agg(json_build_object('id', s.id, 'startDate', s.start_date, 'endDate', s.end_date, 'daysAhead', (s.start_date::DATE - NOW()::DATE)))::JSONB as dates
    FROM event e 
    LEFT JOIN books b ON b.event_id = e.id 
    LEFT JOIN shows s ON s.event_id = e.id 
    LEFT JOIN category c ON e.category_id = c.id 
    LEFT JOIN ticket t ON t.book_id = b.id 
    WHERE (status = 'PUBLISHED' OR status = 'PROMOTED')
    AND s.end_date >= DATE(NOW())
    AND e.is_private = FALSE
    AND s.id = t.show_id
    AND t.canceled = FALSE
    GROUP BY e.id, c.slug
)
SELECT * FROM event_data
LEFT JOIN ticket_summary ts ON ts.book_id = b.book_id
ORDER BY sold;
LIMIT 30;

In this updated query, we use two CTEs: ticket_summary and event_data. The first CTE calculates the ticket count for each book, while the second CTE retrieves event-level data with a new sales column. We then join these two results using a left join to create the final aggregated data.

Conclusion

In this article, we explored how to use the COUNT function with aggregate operations in PostgreSQL. We provided an example query that demonstrates how to perform complex aggregations on multiple tables while producing meaningful results. Additionally, we showed how to simplify the query by using common table expressions (CTEs). By mastering these techniques, you’ll be able to efficiently process large datasets and gain insights from your data.

Last modified on 2024-10-05