mysqlsqlmysql-error-1050mysql-error-1140

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


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.


Solution

  • 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