Question

2985. Calculate Compressed Mean

中文文档

Description

Table: Orders

+-------------------+------+
| Column Name     | Type |
+-------------------+------+
| order_id      | int  |
| item_count    | int  |
| order_occurrences | int  |
+-------------------+------+
order_id is column of unique values for this table.
This table contains order_id, item_count, and order_occurrences.

Write a solution to calculate the average number of items per order, rounded to 2 decimal places.

Return _the result table__ in any order__._

The result format is in the following example.

 

Example 1:

Input: 
Orders table:
+----------+------------+-------------------+
| order_id | item_count | order_occurrences | 
+----------+------------+-------------------+
| 10     | 1      | 500         | 
| 11     | 2      | 1000        |   
| 12     | 3      | 800         |  
| 13     | 4      | 1000        | 
+----------+------------+-------------------+
Output
+-------------------------+
| average_items_per_order | 
+-------------------------+
| 2.70          |
+-------------------------+
Explanation
The calculation is as follows:
 - Total items: (1 * 500) + (2 * 1000) + (3 * 800) + (4 * 1000) = 8900 
 - Total orders: 500 + 1000 + 800 + 1000 = 3300 
 - Therefore, the average items per order is 8900 / 3300 = 2.70

Solutions

Solution 1: Summation

We use the SUM function to calculate the total quantity of products and the total number of orders, then divide the total quantity by the total number of orders to get the average. Finally, we use the ROUND function to round the result to two decimal places.

# Write your MySQL query statement below
SELECT
  ROUND(
    SUM(item_count * order_occurrences) / SUM(order_occurrences),
    2
  ) AS average_items_per_order
FROM Orders;