Sign In
Get Started

Mastering Data Aggregation with PostgreSQL, SQL, and SQLite

by | SQL

Setting Up Your Environment for SQL-Based Data Aggregation

1. Install PostgreSQL

For Linux

sudo apt update
sudo apt install postgresql postgresql-contrib

For macOS using Homebrew

brew update
brew install postgresql
brew services start postgresql

Start PostgreSQL Service

sudo systemctl start postgresql
sudo systemctl enable postgresql

2. Install SQLite

For Linux

sudo apt update
sudo apt install sqlite3

For macOS using Homebrew

brew update
brew install sqlite

For Windows

  1. Download the precompiled binaries from the SQLite website.
  2. Extract the files to a directory of your choice.
  3. Add the directory to your PATH environment variable.

3. Install MySQL

For Linux

sudo apt update
sudo apt install mysql-server

For macOS using Homebrew

brew update
brew install mysql
brew services start mysql

Start MySQL Service

sudo systemctl start mysql
sudo systemctl enable mysql

4. Set Up a Database

PostgreSQL

-- Connect to PostgreSQL
sudo -i -u postgres
psql

-- Create a new database
CREATE DATABASE mydatabase;

-- Connect to the new database
c mydatabase

-- Create a sample table
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(100),
  salary NUMERIC
);

SQLite

# Create a database and a table using SQLite
sqlite3 mydatabase.db

# Inside SQLite prompt
CREATE TABLE employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  department TEXT NOT NULL,
  salary REAL
);

MySQL

-- Connect to MySQL
mysql -u root -p

-- Create a new database
CREATE DATABASE mydatabase;

-- Use the new database
USE mydatabase;

-- Create a sample table
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(100),
  salary DECIMAL(10, 2)
);

5. Insert Sample Data

PostgreSQL

INSERT INTO employees (name, department, salary) 
VALUES
('Alice', 'Engineering', 85000),
('Bob', 'Sales', 70000),
('Charlie', 'HR', 60000);

SQLite

INSERT INTO employees (name, department, salary) 
VALUES
('Alice', 'Engineering', 85000),
('Bob', 'Sales', 70000),
('Charlie', 'HR', 60000);

MySQL

INSERT INTO employees (name, department, salary) 
VALUES
('Alice', 'Engineering', 85000),
('Bob', 'Sales', 70000),
('Charlie', 'HR', 60000);

Your environment is now set up for PostgreSQL, SQLite, and MySQL. You have also created a sample database and an employees table with some sample data to start practicing data aggregation.

Basic Aggregation Functions in PostgreSQL, SQL, and SQLite

PostgreSQL

-- Total Number of items
SELECT COUNT(*) AS total_items FROM your_table;

-- Sum of a column
SELECT SUM(your_column) AS total_sum FROM your_table;

-- Average value of a column
SELECT AVG(your_column) AS average_value FROM your_table;

-- Maximum value of a column
SELECT MAX(your_column) AS max_value FROM your_table;

-- Minimum value of a column
SELECT MIN(your_column) AS min_value FROM your_table;

SQL (Standard SQL)

-- Total Number of items
SELECT COUNT(*) AS total_items FROM your_table;

-- Sum of a column
SELECT SUM(your_column) AS total_sum FROM your_table;

-- Average value of a column
SELECT AVG(your_column) AS average_value FROM your_table;

-- Maximum value of a column
SELECT MAX(your_column) AS max_value FROM your_table;

-- Minimum value of a column
SELECT MIN(your_column) AS min_value FROM your_table;

SQLite

-- Total Number of items
SELECT COUNT(*) AS total_items FROM your_table;

-- Sum of a column
SELECT SUM(your_column) AS total_sum FROM your_table;

-- Average value of a column
SELECT AVG(your_column) AS average_value FROM your_table;

-- Maximum value of a column
SELECT MAX(your_column) AS max_value FROM your_table;

-- Minimum value of a column
SELECT MIN(your_column) AS min_value FROM your_table;

Grouping and Aggregation

PostgreSQL

-- Group by a column and apply aggregation
SELECT your_group_column, COUNT(*) AS total_items, SUM(your_column) AS total_sum
FROM your_table
GROUP BY your_group_column;

SQL (Standard SQL)

-- Group by a column and apply aggregation
SELECT your_group_column, COUNT(*) AS total_items, SUM(your_column) AS total_sum
FROM your_table
GROUP BY your_group_column;

SQLite

-- Group by a column and apply aggregation
SELECT your_group_column, COUNT(*) AS total_items, SUM(your_column) AS total_sum
FROM your_table
GROUP BY your_group_column;

Make sure to replace your_table, your_column, and your_group_column with the actual names used in your database.

Sales Now On Advertisement

Practical Implementation: Grouping Data with GROUP BY

PostgreSQL, SQL, and SQLite

SQL Script

-- Grouping sales data by product and calculating total sales quantity

SELECT 
    product_id,
    COUNT(*) AS sales_count,
    SUM(quantity) AS total_quantity,
    AVG(price) AS average_price
FROM 
    sales
GROUP BY 
    product_id;

Explanation

  • product_id: Column by which data is grouped.
  • COUNT(*) AS sales_count: Count of sales entries per product.
  • SUM(quantity) AS total_quantity: Sum of quantities sold per product.
  • AVG(price) AS average_price: Average price per product.

Example Data for Implementation

-- Example sales table structure
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INTEGER,
    quantity INTEGER,
    price NUMERIC
);

-- Inserting sample data
INSERT INTO sales (product_id, quantity, price) VALUES
(1, 3, 10.00),
(2, 5, 15.00),
(1, 7, 10.00),
(3, 10, 20.00),
(2, 1, 15.50);

Running the Query

-- Executing the query
SELECT 
    product_id,
    COUNT(*) AS sales_count,
    SUM(quantity) AS total_quantity,
    AVG(price) AS average_price
FROM 
    sales
GROUP BY 
    product_id;

Expected Result

product_id | sales_count | total_quantity | average_price
-----------|-------------|----------------|--------------
1          | 2           | 10             | 10.00
2          | 2           | 6              | 15.25
3          | 1           | 10             | 20.00

Use this snippet directly in your SQL environment (PostgreSQL, SQL, or SQLite) to group and aggregate your sales data effectively.

-- Filtering aggregated data using the HAVING clause in PostgreSQL, SQL, and SQLite.

-- Example Table: sales (id, product_id, quantity, price)

-- PostgreSQL Sample Implementation:
SELECT product_id, SUM(quantity) as total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

-- SQL Sample Implementation:
SELECT product_id, SUM(quantity) as total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

-- SQLite Sample Implementation:
SELECT product_id, SUM(quantity) as total_quantity
FROM sales
GROUP BY product_id
HAVING SUM(quantity) > 100;

This implementation allows you to filter the aggregated SUM(quantity) to only include products where the total quantity sold is greater than 100. Apply these snippets to your respective SQL environment.

Advanced Aggregation Techniques

PostgreSQL

Aggregating with Filter Clause

SELECT 
    department,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_tasks,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_tasks
FROM 
    tasks
GROUP BY 
    department;

Window Functions

SELECT 
    department,
    employee_id,
    SUM(salary) OVER (PARTITION BY department) AS total_department_salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
FROM 
    employees;

SQL (ANSI SQL)

Aggregating with CASE Statements

SELECT 
    department,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_tasks,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_tasks
FROM 
    tasks
GROUP BY 
    department;

Aggregating Multiple Columns

SELECT 
    department,
    SUM(sales) AS total_sales,
    AVG(sales) AS avg_sales,
    MAX(sales) AS max_sales
FROM 
    sales_data
GROUP BY 
    department;

SQLite

Aggregating with Subqueries

SELECT 
    d.department,
    d.total_count,
    c.completed_count,
    d.total_count - c.completed_count AS pending_count
FROM 
    (SELECT department, COUNT(*) AS total_count FROM tasks GROUP BY department) d
    LEFT JOIN 
    (SELECT department, COUNT(*) AS completed_count FROM tasks WHERE status = 'completed' GROUP BY department) c
    ON d.department = c.department;

Using Common Table Expression (CTE)

WITH SalesCTE AS (
    SELECT 
        department,
        SUM(sales) as total_sales
    FROM 
        sales_data
    GROUP BY 
        department
)
SELECT 
    s.department,
    s.total_sales,
    AVG(sales) OVER (PARTITION BY s.department) AS avg_sales
FROM 
    SalesCTE s;

These advanced SQL queries are designed for practical implementation across PostgreSQL, SQL standards, and SQLite environments. They demonstrate filtering within aggregates, window functions, complex aggregation, and the application of common table expressions (CTEs).

#6: Performance Optimization for Aggregation Queries

PostgreSQL

-- Use Indexes to speed up grouping and filtering
CREATE INDEX idx_sales_order_date ON sales(order_date);

-- Optimize with MATERIALIZED VIEW
CREATE MATERIALIZED VIEW mv_sales_summary AS
SELECT 
    product_id, 
    SUM(quantity) AS total_quantity, 
    AVG(price) AS avg_price
FROM sales
GROUP BY product_id;

-- Use the materialized view in your aggregation queries
SELECT 
    product_id, 
    total_quantity, 
    avg_price
FROM mv_sales_summary
WHERE total_quantity > 100;

SQL (Generic SQL-Compatible Engines)

-- Use Indexes if supported by your SQL database
CREATE INDEX idx_sales_order_date ON sales(order_date);

-- Use TEMPORARY TABLE for repeated aggregation queries during a session
CREATE TEMPORARY TABLE temp_sales_summary AS
SELECT 
    product_id, 
    SUM(quantity) AS total_quantity, 
    AVG(price) AS avg_price
FROM sales
GROUP BY product_id;

-- Use the temporary table in your aggregation queries
SELECT 
    product_id, 
    total_quantity, 
    avg_price
FROM temp_sales_summary
WHERE total_quantity > 100;

SQLite

-- SQLite automatically creates indexes on PRIMARY KEYs
-- Optimize by creating indexes manually where suitable
CREATE INDEX idx_sales_order_date ON sales(order_date);

-- Optimize with INDEXED VIEW (SQLite does not support materialized views)
CREATE INDEX idx_optimized_sales_view ON sales(product_id);

-- Use this index in your aggregation queries
SELECT 
    product_id, 
    SUM(quantity) AS total_quantity, 
    AVG(price) AS avg_price
FROM sales 
USE INDEX(idx_optimized_sales_view)  -- Forced index usage
GROUP BY product_id
HAVING total_quantity > 100;

Note: Ensure that the changes are consistently applied across your database schema and that your indexes are updated as needed based on changes in your data and schema.

Related Posts