Section 02 : Basic Agg : part 01 - solved error in p2

Farahat612 · Farahat612 · commit d419331e5732 · 2024-04-06T23:54:15.000+02:00
diff --git a/3. Basic Aggregate Functions/02 1251. Average Selling Price.sql b/3. Basic Aggregate Functions/02 1251. Average Selling Price.sql
@@ -1,4 +1,14 @@
-SELECT u.product_id, ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price
-FROM Prices p
-JOIN UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date
-GROUP BY u.product_id;
+SELECT
+    p.product_id,
+    COALESCE(ROUND(SUM(p.price * COALESCE(u.units, 0)) / GREATEST(SUM(u.units), 1), 2), 0) AS average_price
+FROM
+    Prices p
+LEFT JOIN
+    UnitsSold u
+ON
+    p.product_id = u.product_id
+    AND u.purchase_date BETWEEN p.start_date AND p.end_date
+GROUP BY
+    p.product_id;
+
+-- Solved the error by modifying the SQL query to include products from the Prices table that have no corresponding entries in the UnitsSold table. This was achieved by using a LEFT JOIN and COALESCE to handle the cases where there are no matching units sold for a product.
