Here's an example SELECT statement that uses aggregate window functions to calculate the total due for all vendors and the total due for each vendor:
SELECT
vendor_id,
SUM(amount_due) OVER () AS total_due_all_vendors,
SUM(amount_due) OVER (PARTITION BY vendor_id) AS total_due_per_vendor
FROM
invoices;
In this example, we're querying a table called invoices that contains information about invoices issued to different vendors. The table has columns for the vendor ID, the amount due on each invoice, and other information.
The SELECT statement uses two aggregate window functions to calculate the total due for all vendors and the total due for each vendor:
The first window function, SUM(amount_due) OVER (), calculates the total amount due for all vendors by summing up the amount_due column for all the rows in the result set. The OVER () clause indicates that the sum should be calculated over all rows in the result set.
The second window function, SUM(amount_due) OVER (PARTITION BY vendor_id), calculates the total amount due for each vendor by summing up the amount_due column for all the rows in the result set that have the same vendor_id. The OVER (PARTITION BY vendor_id) clause indicates that the sum should be calculated separately for each distinct value of vendor_id.
The result set will include three columns: vendor_id, which identifies the vendor; total_due_all_vendors, which gives the total amount due for all vendors; and total_due_per_vendor, which gives the total amount due for each vendor.