= 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)