【SQL】SELECT中加入DECIMAL

Table: Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id, start_date, end_date) is the primary key for this table.
Each row of this table indicates the price of the product_id in the period from start_date to end_date.
For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.

Table: UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
There is no primary key for this table, it may contain duplicates.
Each row of this table indicates the date, units, and product_id of each product sold. 

Write an SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places.

SOLUTION：CAST(column AS DECIMAL(a,b)) AS new_column

                        a代表小数+整数的位数，b代表小数位数

SELECT prices.product_id,CAST(SUM(price*units)/SUM(units) AS DECIMAL(10,2)) as average_price FROM prices,unitssold
WHERE prices.product_id=unitssold.product_id and start_date<=purchase_date and purchase_date<=end_date
GROUP BY prices.product_id;