If I used this query, it returns a separate recordset that contains the sum of all the invoices, how can I put the sum into a field in an existing table?


declare @daytolook datetime
select @daytolook = convert(datetime, '5-1-2005', 102)


                SELECT distinct dbo.suppliers.supplierid,
              dbo.invoices.InvoiceID, dbo.invoices.DueDate,
              dbo.invoices.AmountToPay,invoicedetail.Quantity * invoicedetail.Unitprice as linetotal , dbo.invoices.Paid
          FROM dbo.invoices INNER JOIN
                      dbo.invoicedetail ON dbo.invoices.InvoiceID = dbo.invoicedetail.InvoiceID INNER JOIN
                      dbo.LineItems ON dbo.invoicedetail.LineItemID = dbo.LineItems.LineItemID INNER JOIN
                      dbo.suppliers ON dbo.invoices.SupplierID = dbo.suppliers.SupplierID
              WHERE (dbo.invoices.DueDate <= @daytolook) and (dbo.invoices.Paid = 0)
        group by dbo.suppliers.supplierid,
              dbo.invoices.InvoiceID, dbo.invoices.DueDate,
              dbo.invoices.AmountToPay,invoicedetail.Quantity, invoicedetail.Unitprice ,invoicedetail.Quantity * invoicedetail.Unitprice, dbo.invoices.Paid
        order by dbo.suppliers.supplierid,
              dbo.invoices.InvoiceID, dbo.invoices.DueDate,
              dbo.invoices.AmountToPay,invoicedetail.Quantity * invoicedetail.Unitprice , dbo.invoices.Paid
        compute sum (invoicedetail.quantity * invoicedetail.unitprice) by dbo.suppliers.supplierid,
              dbo.invoices.InvoiceID, dbo.invoices.DueDate

__________________
---------------------------
\"I finally understand the true value of time, for it is the bonds between us that give time its meaning.\"

How about putting the results of this query into temp table, then joining that temp table to whatever is necessary to move that sum column where you want?

Stoney