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.
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 |