I have a table:

SELECT
    categ.category_name AS 'Product Category',
    SUM(order.quantity) AS 'Number Purchased',
    SUM(order.discount) AS 'Total Discount $',
    SUM(order.price) AS 'Total Price',
    (SUM(products.price) - SUM(order.discount)) AS 'Total Sales'
FROM
    products
LEFT JOIN
    orders USING (product_id)
LEFT JOIN
    categ USING (category_id)
GROUP BY
    categ.category_id
ORDER BY
    categ.category_name

I'm trying to figure out the best way to produce the sum of the columns in the last row, demonstrated below:

so far I've tried:

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, 
    SUM('Number Purchased'), 
    SUM('Total Discount $'), 
    SUM('Total Price'),
    SUM('Total Sales')
FROM
    products
LEFT JOIN
    orders USING (product_id)
LEFT JOIN
    categ USING (category_id)
GROUP BY
    categ.category_id
ORDER BY
    categ.category_name

doesn't seems to be working, any suggestions?

CodePudding user response：

You can do that programatically in your application, that might be the fastest implementation. If you really want to aggregate in MySQL (which executes a second sub-query) and do UNION ALL, then just fix the second part.

You don't need to GROUP BY there:

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
ORDER BY
    categ.category_name
 
UNION ALL

SELECT  
    NULL, 
    SUM(order_items.quantity),
    SUM(order_items.discount_amount),
    SUM(order_items.item_price),
    SUM(order_items.item_price) - SUM(order_items.discount_amount)
    products
LEFT JOIN
    orders USING (product_id)
LEFT JOIN
    categ USING (category_id)

CodePudding user response：

Using UNION does not enforce the sorting order by the ORDER BY clause unfortunately. Try using with rollup in the group by clause:

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