Group By (learnsqlonline.org)

The GROUP BY clause in SQL is used to group rows from a table based on one or more columns. It enables us to aggregate data and perform calculations such as counting, summing, averaging, or finding the maximum and minimum values within each group. This powerful clause forms the foundation for data analysis and reporting in SQL.

--Group by
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT
);

INSERT INTO customers (first_name, last_name) VALUES
    ("John", "Doe");

The GROUP BY clause simplifies data analysis by providing a concise way to summarize information across different groups. It allows us to answer questions like “How many customers are in each city?” or “What is the total revenue per product category?” By grouping and aggregating data, we can gain valuable insights and make informed business decisions.

--Group By Cont.
INSERT INTO orders (customer_id, product_name) VALUES
    (last_insert_rowid(), "Coke"),
    (last_insert_rowid(), "Sprite");

INSERT INTO customers (first_name, last_name) VALUES
    ("Eric", "Smith");

INSERT INTO orders (customer_id, product_name) VALUES
    (last_insert_rowid(), "Doritos");

.mode column
.headers on
SELECT first_name, last_name, COUNT(*) AS total_orders FROM customers
JOIN orders ON orders.customer_id = customers.id
GROUP BY orders.customer_id;

The GROUP BY clause extends the capabilities of SQL queries by enabling us to apply aggregate functions to subsets of data. This allows us to perform calculations and generate summary statistics within each group. The ability to break down data into meaningful groups empowers us to uncover patterns, identify trends, and extract valuable information from large datasets.

Group By Exercise Solution

--Group By Exercise
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT
);

INSERT INTO customers (first_name, last_name) VALUES
    ("John", "Doe");

INSERT INTO orders (customer_id, product_name) VALUES
    (last_insert_rowid(), "Coke"),
    (last_insert_rowid(), "Sprite");

INSERT INTO customers (first_name, last_name) VALUES
    ("Eric", "Smith");

INSERT INTO orders (customer_id, product_name) VALUES
    (last_insert_rowid(), "Doritos");

--Completed Code
SELECT first_name, COUNT(*) AS total_orders FROM customers
JOIN orders ON orders.customer_id = customers.id
GROUP BY orders.customer_id;

The GROUP BY clause in SQL offers a powerful and efficient way to group data, perform calculations, and gain insights from relational databases. By utilizing this clause, we can simplify complex data analysis tasks, generate summary statistics, and make informed business decisions. Mastering the GROUP BY clause expands our SQL query capabilities and allows us to uncover meaningful patterns within our data.