SQL Query: Showing multiple data and total order values

Question

As part of an SQL Queries assignment, I am required to meet the following criteria:

"Display all customers who have bought anything in the last 6 months. Show >customer name, loyalty card number, date of order, and total value of order. >Ensure this is named correctly in the query results as Total_Order_Value."

For this, I came up with a script which has been marked as wrong. I am confused by the feedback as I believe I have met the question criteria. Find the script and feedback below:

Script

SELECT Aorder.*, Acustomer.*, AorderDetails.quantity, (AorderDetails.quantity *AmenuItem.itemCost) AS Total_Order_Value 
FROM Aorder, Acustomer, AmenuItem, AorderDetails
WHERE orderDateTime < Now() AND orderDateTime > DATE_ADD(Now(), INTERVAL -6 MONTH)
AND Acustomer.customerID = Aorder.customerID 
AND Aorder.orderID = AorderDetails.orderID
AND AorderDetails.itemID = AmenuItem.itemID
AND Aorder.paymentType IN ('Cash' , 'Card');

Feedback

"At the moment this will multiply cost *qty for each individual item bought in one order. You need the total value for each order. I.e. at the moment I would see a value for each item I bought in one order, I would like to see the total for the whole order. You need to add an aggregate and a group by"

I would appreciate any assistance in helping me understand what went wrong and how I may structure this correctly to meet the requirements. Thank you in advance.

Answer 1

Essentially, you are reporting results at the orders items level and not customers and orders level as the original question asked. Your current resultset likely repeats customers and order details for each corresponding item which can be lengthy with its one-to-many relationships.

To resolve, simply refactor your SQL statement into an aggregate query that groups on customer and order items and sums each order item's value to retrieve the total amount of whole order. Additionally heed best practices in SQL:

1. EXPLICIT JOIN: As mentioned in comments do not use the old-join style of commas in FROM clause with matching conditions in WHERE. This is known as implicit joins. The current standard introduced in ANSI-92 emphasizes explicit joins using JOIN and ON clauses. While this does not change efficiency or output, it does aid in readability and maintainability.

2. SELECT CLAUSE: Try avoiding selecting all fields in tables with Aorder.*, Acustomer.* which is an open-ended resultset output. Your question specifically asked for certain fields: customer name, loyalty card number, date of order, and total value of order. So, select them accordingly.

3. TABLE ALIASES: For longer table names and tables that share the same prefixes, stems, or suffixes like your A tables, use table aliases that properly abbreviates and defines your identifiers. Again this practice should not change output but aids in readability and maintainability.

See below working SQL statement (adjust field names to actuals).

SELECT c.customer_name, 
       c.loyalty_card_number, 
       CAST(o.orderDateTime AS DATE)  AS Order_Date,                      
       SUM(d.quantity * m.itemCost) AS Total_Order_Value 

FROM Aorder o
INNER JOIN Acustomer c ON c.customerID = o.customerID 
INNER JOIN AorderDetails d ON o.orderID = d.orderID
INNER JOIN AmenuItem m ON d.itemID = m.itemID
WHERE o.orderDateTime < Now() 
  AND o.orderDateTime > DATE_ADD(Now(), INTERVAL -6 MONTH)
  AND o.paymentType IN ('Cash' , 'Card')

GROUP BY c.customer_name, 
         c.loyalty_card_number, 
         CAST(o.orderDateTime AS DATE)

NOTE: Do not make the mistake as many MySQL users do in excluding non-aggregated columns in GROUP BY clause of an aggregate query which is required in ANSI-SQL. Asterisks, *, should never be used in aggregate queries. MySQL unfortunately allows this feature with its ONLY FULL GROUP BY mode turned off and can return unreliable results.