CIS课程SQL项目技术求助：查询各订单的图书相关统计数据

Hey there! Let's figure out how to build that query you need—extending Query #9 to include total quantity and average unit price for each order.

First, let's recap the core requirement: we need to group data by each order number (ono) and calculate three metrics alongside it:

• Number of unique book types (using bno, so we'll count distinct values here)
• Total quantity of books in the order
• Average unit price of the books in the order

Assuming you have an order details table (let's say it's named order_details—adjust if your table name is different) with columns ono, bno, quantity, and price, here's the working SQL statement:

SELECT
  ono,
  COUNT(DISTINCT bno) AS book_type_count,
  SUM(quantity) AS total_book_quantity,
  AVG(price) AS average_unit_price
FROM order_details
GROUP BY ono;

Let's break down what each part does: #

• COUNT(DISTINCT bno): This counts how many unique book numbers are in each order—exactly what Query #9 was doing for book types. Without DISTINCT, it would count every line item (even if the same book is ordered multiple times), which isn't what we want for "种类数".
• SUM(quantity): Adds up all the quantity values for each order to get the total number of books shipped/ordered.
• AVG(price): Calculates the average of the unit prices for all items in the order. If your price is stored in a separate books table linked by bno, you'll need a JOIN—here's how that would look:

  SELECT
    od.ono,
    COUNT(DISTINCT od.bno) AS book_type_count,
    SUM(od.quantity) AS total_book_quantity,
    AVG(b.price) AS average_unit_price
  FROM order_details od
  JOIN books b ON od.bno = b.bno
  GROUP BY od.ono;
  

Common mistakes that might have caused your error: #

• Forgetting to GROUP BY ono: SQL requires grouping by all non-aggregated columns in your SELECT clause.
• Using COUNT(bno) instead of COUNT(DISTINCT bno): This would count line items, not unique book types.
• Misreferencing column names (typos, or using the wrong table alias if joining).

If you run this in SQL Fiddle, it should output exactly what you need—for example, order 1020 would show book_type_count as 4, plus the total quantity and average price for that order.

内容的提问来源于stack exchange，提问作者Soft_ibo