0

I have a table of Orders from Customers in my database for a retail store (using My SQL) and I am trying to do a ROLL UP of all orders for the month of April and for the final row to have a title of 'Grand Total' next to this total sum figure:

SELECT coalesce (OrderID, 'GrandTotal') AS OrderID, 
SUM(OrderPrice) as SumOrderPrice
FROM ORDERS 
WHERE OrderDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY OrderPrice WITH ROLLUP;

The problem I am having is that when I run this code, next to the total 'Sum' figure, I am getting a duplicate OrderID number, not the text 'Grand Total'. Any ideas where I'm going wrong? Many thanks

2

I'm not sure what it is you are trying to do, the GROUP BY clause that you provided is invalid. Is the following what you meant?

SELECT coalesce (OrderID, 'GrandTotal') AS OrderID, 
       SUM(OrderPrice) as SumOrderPrice
FROM ORDERS 
WHERE OrderDate BETWEEN '2018-04-01' AND '2018-04-30'
GROUP BY OrderID WITH ROLLUP;

I.e. GROUP BY OrderID instead of OrderPrice?

  • Hi Lennart, thank you, yes that's where my error was, the GROUP BY should be by OrderID – Dominic Callan Apr 13 '18 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.