mysql 分组后求百分比

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_category VARCHAR(50),
    amount DECIMAL(10, 2)
);

SELECT product_category, SUM(amount) AS total_amount
FROM sales
GROUP BY product_category;

SELECT SUM(amount) AS grand_total_amount
FROM sales;

SELECT 
    s.product_category, 
    s.total_amount, 
    (s.total_amount / g.grand_total_amount) * 100 AS percentage
FROM (
    SELECT product_category, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product_category
) s,
(
    SELECT SUM(amount) AS grand_total_amount
    FROM sales
) g;

SELECT 
    s.product_category, 
    s.total_amount, 
    CAST(s.total_amount AS DECIMAL(10, 2)) / CAST(g.grand_total_amount AS DECIMAL(10, 2)) * 100 AS percentage
FROM (
    SELECT product_category, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product_category
) s,
(
    SELECT SUM(amount) AS grand_total_amount
    FROM sales
) g;

SELECT 
    c.product_category, 
    COALESCE(s.total_amount, 0) AS total_amount, 
    (COALESCE(s.total_amount, 0) / g.grand_total_amount) * 100 AS percentage
FROM (
    SELECT DISTINCT product_category
    FROM sales
) c
LEFT JOIN (
    SELECT product_category, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product_category
) s ON c.product_category = s.product_category,
(
    SELECT SUM(amount) AS grand_total_amount
    FROM sales
) g;