Write a query to find the total sales per product.

Let's walk through how to write a SQL query to find the total sales per product, along with:

• Creating a dummy sales table with relevant columns
• Inserting sample data
• Querying total sales per product
• Showing expected output

Objective:

"Write a query to find the total sales per product."

Step 1: Create sales Table

We'll create a table to store sales records. A typical sales table might include:

• sale_id: Primary key
• product_name: Name of the product sold
• quantity: Number of units sold
• price_per_unit: Price per unit of the product
• sale_date: Date of sale

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT,
    price_per_unit DECIMAL(10, 2),
    sale_date DATE
);

Step 2: Insert Dummy Data

Here is some sample data you can use:

INSERT INTO sales (sale_id, product_name, quantity, price_per_unit, sale_date) VALUES
(1, 'Laptop', 2, 55000.00, '2024-06-01'),
(2, 'Phone', 5, 20000.00, '2024-06-03'),
(3, 'Tablet', 3, 15000.00, '2024-06-05'),
(4, 'Laptop', 1, 55000.00, '2024-06-10'),
(5, 'Phone', 2, 20000.00, '2024-06-11'),
(6, 'Tablet', 4, 15000.00, '2024-06-13');

Step 3: Query – Total Sales per Product

To calculate total sales, multiply quantity * price_per_unit and group by product_name:

SELECT 
    product_name,
    SUM(quantity * price_per_unit) AS total_sales
FROM 
    sales
GROUP BY 
    product_name;

Step 4: Output – Total Sales per Product

Explanation:

• Laptop: (2 × 55000) + (1 × 55000) = 110000 + 55000 = 165000
• Phone: (5 × 20000) + (2 × 20000) = 100000 + 40000 = 140000
• Tablet: (3 × 15000) + (4 × 15000) = 45000 + 60000 = 105000

Summary

• We created a sales table with quantity and price data.
• We wrote a SQL query using SUM() and GROUP BY to calculate total sales per product.
• Output shows total sales per product.