mysql对最后一行多列中的值求和的最佳方式

Question

我有一个表：

总价格>总销售额 1000>1000节>9100节null

产品类别		购买数量	总折扣$
汽车1	100	900
第2节	200	2000	1800
第3节	9节	900节
汽车4	null	null

您可以在应用程序中用程序实现这一点，这可能是最快的实现。如果您真的想在MySQL中聚合(执行第二个子查询(并执行UNION ALL，那么只需修复第二部分。

你不需要GROUP BY：

SELECT *
FROM 
(SELECT
categories.category_name AS 'Product Category',
SUM(order_items.quantity) AS 'Number Purchased',
SUM(order_items.discount_amount) AS 'Total Discount $',
SUM(order_items.item_price) AS 'Total Price',
(SUM(order_items.item_price) - SUM(order_items.discount_amount)) AS 'Total Sales'
FROM
products
LEFT JOIN
orders USING (product_id)
LEFT JOIN
categories USING (category_id)
GROUP BY
categories.category_id

UNION ALL
SELECT  
NULL AS 'Product Category',
SUM(order_items.quantity) AS 'Number Purchased',
SUM(order_items.discount_amount) AS 'Total Discount $',
SUM(order_items.item_price) AS 'Total Price',
(SUM(order_items.item_price) - SUM(order_items.discount_amount)) AS 'Total Sales'
products
LEFT JOIN
orders USING (product_id)
LEFT JOIN
categ USING (category_id)
) t
ORDER BY 'Product Category'

不幸的是，

使用UNION并不能通过ORDER BY子句强制执行排序顺序。尝试在group-by子句中使用with rollup：

select `Product Category`,sum(`Product Category`),sum(`Total Discount $`),sum(`Total Discount $`),sum(`Total Price`) 
from
(SELECT
categ.category_name AS 'Product Category',
SUM(orders.quantity) AS 'Product Category',
SUM(orders.discount) AS 'Total Discount $',
SUM(orders.price) AS 'Total Price',
(SUM(products.price) - SUM(orders.discount)) AS 'Total Sales'
FROM
products
LEFT JOIN
orders USING (product_id)
LEFT JOIN
categ USING (category_id)
GROUP BY
categ.category_id) t
GROUP BY
`Product Category` with rollup
ORDER BY
`Product Category`
;