Issues with ORDER BY caused by changing the DATE_FORMAT in the SELECT statement

Question

I'm doing a Databases assignment for Uni and I'm stuck. I'm 99% of the way there. The brief is:

Create a query to view the annual sales over the last 12 months of the business showing the total number of products sold and the total price sold every month (use a group by with roll-up)

SELECT date_format(_order.orderDate, '%M') AS 'Month',
       sum(orderItem.quantity) AS 'Total Items Purchased',
       sum(orderItem.unitPrice * orderItem.quantity) AS 'Total Price' 
FROM orderItem
LEFT JOIN _order ON orderItem.orderID = _order.orderID
WHERE orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY date_format(_order.orderDate, '%M') WITH ROLLUP
ORDER BY date_format(_order.orderDate, '%M');

I'm pretty sure this fulfils the brief even if the months are out of order but it's driving me insane. The dates in my "_order" table (I know now not to use keywords in my database) are in the usual MySQL format, YYYY-MM-DD, so I changed them into just months in my SELECT statement so I could roll the data up into neat totals for each month. But this query keeps giving me the list of months in alphabetical order.

I've tried a huge amount of suggestions for ordering by date that I've found on here and other websites but every time I change the GROUP BY part, I get error 1140 about it being incompatible with sql_mode=only_full_group_by, and every time I change the ORDER BY, I get error 1055 which is also about it being incompatible with sql_mode=only_full_group_by.

I know I can disable sql_mode=only_full_group_by but I would really like to know what I'm doing wrong. Learning is important and all that.

More info: These are my "_order" and "orderItem" tables.

CREATE TABLE IF NOT EXISTS _order(
   orderID VARCHAR(10), 
   customerID VARCHAR(10), 
   staffID VARCHAR(10), 
   orderDate DATE NOT NULL,
   deliveryDate DATE NOT NULL,
   deliveryStreet VARCHAR(50) NOT NULL,
   deliveryCounty VARCHAR(20) NOT NULL,
   deliveryEircode VARCHAR(8) NOT NULL,  
   orderStatus VARCHAR(50) NOT NULL,
   PRIMARY KEY (orderID)
);

CREATE TABLE IF NOT EXISTS orderItem(
   itemID VARCHAR(10), 
   orderID VARCHAR(10), 
   productID VARCHAR(10), 
   quantity INT NOT NULL, 
   unitPrice DECIMAL(12, 2) NOT NULL,
   PRIMARY KEY (itemID)
);

These are examples of the data I've inserted into my "_order" and "orderItem" tables.

INSERT INTO _order (orderID, customerID, staffID, orderDate, deliveryDate, deliveryStreet, deliveryCounty, deliveryEircode, orderStatus) VALUES ('SO65301830', 'SC45398810', 'SE55900726', '2023-05-28', '2023-05-30', '0 Aberg Parkway', 'Mayo', 'A08 OY80', 'Out for Delivery');

INSERT INTO orderItem (itemID, orderID, productID, quantity, unitPrice) VALUES ('OL127', 'SO65301830', 'SP07', 4, 14.99);

What I'm trying to do is take all the quantities and unit prices in all my order items, add them all up, and display them alongside the month they were sold in. The statement I have above gives me:

Month	Total Items Purchased	Total Sales
April	54	6444.46
August	41	11709.59
December	30	10704.70

Etc.

What I want is:

Month	Total Items Purchased	Total Sales
January	28	6204.72
February	27	6909.73
March	47	13834.53

Etc.

Hope this clears things up. Sorry, this is my first post.

This query doesn't. That's the only one that kind of works but it doesn't order the results by date. Like I said, when I try to change the ORDER BY statement in any way, I get error 1055. — iGleeson, Apr 14 '23 at 20:45
add some data and wanted result, that would help understand your problem, also yo should group and order by month and year — nbk, Apr 14 '23 at 20:47

score 0 · Accepted Answer · answered Apr 14 '23 at 21:13

0

You can add an aggregation to your order to get your wanted result

SELECT date_format(_order.orderDate, '%M') AS 'Month',
       sum(orderItem.quantity) AS 'Total Items Purchased',
       sum(orderItem.unitPrice * orderItem.quantity) AS 'Total Price' 
FROM orderItem
LEFT JOIN _order ON orderItem.orderID = _order.orderID
WHERE orderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY date_format(_order.orderDate, '%M') WITH ROLLUP
ORDER BY  MIN(DATE_FORMAT(_order.orderDate,'%Y%m'));

Month	Total Items Purchased	Total Price
May	4	59.96
null	8	119.92
August	4	59.96

fiddle

answered Apr 14 '23 at 21:13

nbk

45,398
8
30
47

Oh my God, I've been beating my head against a wall for ages trying to get this to do what I want. You're smart. I hate to be that guy but is there any way to make row created by the ROLLUP be at the bottom. If not, I will learn to live with it. I am so grateful. I can't even upvote your answer because I don't have enough reputation. I hope everyone who sees this, does. – iGleeson Apr 14 '23 at 21:20
you have now enough points to accept the answer and upvote – nbk Apr 14 '23 at 21:22
I've accepted the answer. I'm still 2 points short but I'll go make 2 points and come back, don't you worry! – iGleeson Apr 14 '23 at 21:24
FYI, I just replaced "min" with "max" while fiddling around with your answer and it put the ROLLUP row at the bottom. Thanks again man, I really appreciate it. – iGleeson Apr 14 '23 at 21:33

Issues with ORDER BY caused by changing the DATE_FORMAT in the SELECT statement

1 Answers1