mysql best way to sum values in multiple column in the last row

user_19081796 0
Viewed 62

I have a table:

Product Category Number Purchased Total Discount $ Total Price Total Sales
car 1 1 100 1000 900
car 2 2 200 2000 1800
car 3 9 900 10000 9100
car 4 null null null null 
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:

Product Category Number Purchased Total Discount $ Total Price Total Sales
car 1 1 100 1000 900
car 2 2 200 2000 1800
car 3 9 900 10000 9100
car 4 null null null null
null 12 1200 13000 11800

so far I've tried:

SELECT
    categ.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
    categ USING (category_id)
GROUP BY
    categ.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)
ORDER BY
    categ.category_name

doesn't seems to be working, any suggestions?

aggregate mysql
2 Answers

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 *
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'
user_14379880

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

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`
;
user_185543690
Related