Orders ordered by order total from highest to lowest

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)