Changes between Initial Version and Version 1 of AdvancedReport1

Timestamp:

08/21/26 05:16:19 (30 hours ago)

Author:

235018

Comment:

--

AdvancedReport1

@@ +1 @@
+= Orders ordered by order total from highest to lowest
+{{{#!sql
+CREATE OR REPLACE FUNCTION get_orders_by_total()
+RETURNS TABLE (
+    order_num INT,
+    client_id INT,
+    client_name TEXT,
+    order_quantity INT,
+    order_status TEXT,
+    payment_method TEXT,
+    discount NUMERIC,
+    order_total NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        o.order_num,
+        c.client_id,
+        c.name,
+        o.quantity,
+        o.status,
+        o.payment_method,
+        COALESCE(o.discount, 0) AS discount,
+        SUM(p.price * o.quantity)
+            * (1 - COALESCE(o.discount, 0) / 100) AS order_total
+    FROM "order" o
+    JOIN makes_order mo
+        ON o.order_num = mo.order_num
+    JOIN client c
+        ON mo.client_id = c.client_id
+    JOIN includes i
+        ON o.order_num = i.order_num
+    JOIN product p
+        ON i.code = p.code
+    GROUP BY
+        o.order_num,
+        c.client_id,
+        c.name,
+        o.quantity,
+        o.status,
+        o.payment_method,
+        o.discount
+    ORDER BY order_total DESC;
+END;
+$$;
+
+}}}
+
+== Relational Algebra
+- P(code, price, availability, description, ...)
+- O(order_num, quantity, status, last_modified_date, payment_method, discount)
+- I(code, order_num)
+- MO(client_id, order_num)
+- C(client_id, name, first_name, last_name, ...)
+
+**JOIN orders with client lists:**
+- J1 ← O ⨝O.order_num = MO.order_num MO
+- J2 ← J1 ⨝MO.client_id = C.client_id C
+
+**JOIN orders with their included products:**
+- J3 ← J2 ⨝O.order_num = I.order_num I
+- J4 ← J3 ⨝I.code = P.code P
+
+**Calculate the total order value:**
+- **FORMULA:** order_total = Σ(P.price × O.quantity) × (1 - COALESCE(O.discount, 0) / 100)
+
+- T ← γorder_num, client_id, client_name, quantity,
+     status, payment_method, discount;
+     Σ(P.price × quantity)
+     × (1 - COALESCE(discount, 0) / 100)
+     → order_total(J4)
+
+**Sort by order total:**
+- R_final ← τorder_total DESC(T)
+
+