Sum of Sums

  • Thread starter Thread starter aa
  • Start date Start date
A

aa

I have a table containing invoice number, line item and the value of that item.
One invoice can contain more tham one item

Query like that:

SELECT [InvoiceNo], Sum([ItemValue]) AS InvoiceTotal
FROM sales
GROUP BY [InvoiceNo];

lists all the invoices with invoice's totals

How do I make this query to calculate and show the GrantTotal (i.e. the sum of InvoiceTotal)?
I tried is as Sum(Sum([ItemValue])) but it returned an error sayng I cannot use nested sums
 
SELECT CStr([InvoiceNo]) As Invoice, Sum([ItemValue]) AS InvoiceTotal
FROM sales
GROUP BY [InvoiceNo]
UNION
SELECT "Grand Total" As Invoice, Sum([ItemValue]) As InvoiceTotal
FROM sales;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I have a table containing invoice number, line item and the value of that
item.
One invoice can contain more tham one item

Query like that:

SELECT [InvoiceNo], Sum([ItemValue]) AS InvoiceTotal
FROM sales
GROUP BY [InvoiceNo];

lists all the invoices with invoice's totals

How do I make this query to calculate and show the GrantTotal (i.e. the sum
of InvoiceTotal)?
I tried is as Sum(Sum([ItemValue])) but it returned an error sayng I cannot
use nested sums
 
Back
Top