SQL การใช้งานฟังก์ชัน SUM

CREATE TABLE Sales (
    sale_id INT,
    product_name VARCHAR(50),
    quantity INT,
    price_per_unit DECIMAL(10, 2),
    sale_date DATE
);

INSERT INTO Sales (sale_id, product_name, quantity, price_per_unit, sale_date)
VALUES
(1, 'Product A', 10, 100.00, '2024-10-01'),
(2, 'Product B', 5, 200.00, '2024-10-02'),
(3, 'Product A', 8, 100.00, '2024-10-03'),
(4, 'Product C', 12, 150.00, '2024-10-01'),
(5, 'Product B', 7, 200.00, '2024-10-02');

SELECT SUM(quantity * price_per_unit) AS total_sales
FROM Sales;

SELECT product_name, SUM(quantity * price_per_unit) AS total_sales_per_product
FROM Sales
GROUP BY product_name;

SELECT SUM(quantity * price_per_unit) AS total_sales_on_date
FROM Sales
WHERE sale_date = '2024-10-02';

CREATE TABLE Customers (
    customer_id INT,
    sale_id INT,
    customer_name VARCHAR(50)
);

INSERT INTO Customers (customer_id, sale_id, customer_name)
VALUES
(1, 1, 'Customer 1'),
(2, 2, 'Customer 2'),
(3, 3, 'Customer 1'),
(4, 4, 'Customer 3'),
(5, 5, 'Customer 2');

SELECT customer_name, SUM(quantity * price_per_unit) AS total_sales_per_customer
FROM Sales
JOIN Customers ON Sales.sale_id = Customers.sale_id
GROUP BY customer_name;