mysql 按属性统计

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    quantity INT,
    price DECIMAL(10, 2),
    sale_date DATE
);

SELECT product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name;

SELECT product_name, SUM(quantity) AS total_quantity, SUM(quantity * price) AS total_sales
FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_name;

SELECT product_name, COUNT(DISTINCT id) AS unique_sales
FROM sales
GROUP BY product_name;

CREATE INDEX idx_product_name ON sales(product_name);

SELECT COALESCE(product_name, 'Unknown') AS product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY COALESCE(product_name, 'Unknown');