mysql - 按年份和季度排序后添加百分比 MySQL

Question

以下查询返回年度和季度的订单总计。我还希望将值(value)表示为总支出的百分比。

SELECT DATABASE() AS PURCHASING_Total_PO_Value_By_Year, 
YEAR(order_headers.created_at) AS Year, 
QUARTER(order_headers.created_at) AS Quarter, 
COUNT(DISTINCT(order_headers.id)) AS Orders, 
SUM(order_lines.accounting_total) AS Order_Total 
FROM (order_lines LEFT JOIN order_headers ON order_lines.order_header_id = order_headers.id) 
WHERE (order_headers.status LIKE 'Issued') OR (order_headers.status LIKE 'Closed') 
Group by YEAR(order_headers.created_at), QUARTER(order_headers.created_at)

我需要将 SUM(order_lines.accounting_total) 作为“总支出”，而不应用年份和季度过滤器。

任何指示/提示将不胜感激。

Answer 1

如果您确实只想要 Order_Total 的总计，没有年份或季度聚合，那么您可以尝试使用子查询:

SELECT DATABASE() AS PURCHASING_Total_PO_Value_By_Year, 
    YEAR(order_headers.created_at) AS Year, 
    QUARTER(order_headers.created_at) AS Quarter, 
    COUNT(DISTINCT(order_headers.id)) AS Orders, 
    SUM(order_lines.accounting_total) AS Order_Total,
    100 * SUM(order_lines.accounting_total) /
        (SELECT SUM(accounting_total) FROM order_lists) AS Order_Percentage
FROM order_lines LEFT JOIN order_headers
    ON order_lines.order_header_id = order_headers.id
WHERE (order_headers.status LIKE 'Issued') OR (order_headers.status LIKE 'Closed')
GROUP BY YEAR(order_headers.created_at), QUARTER(order_headers.created_at)

我添加到您的 SELECT 中的列是这样的:

100 * SUM(order_lines.accounting_total) /
    (SELECT SUM(accounting_total) FROM order_lists) AS Order_Percentage