sum of sum of columns
sum of sum of columns
(OP)
Hi!
I'm using access and I have 3 tables: "Customers", "Invoices" and "InvoiceItems"
Invoices
------------------
Folio IdCostum
------------------
1 1
2 3
3 3
4 2
5 1
6 2
Customers
--------------------------------
IdCostum Name
--------------------------------
1 John
2 Steve
3 Joe
InvoiceItems
------------------------------
Folio Item Total
------------------------------
1 anything $10
1 anything $10
2 anything $30
3 anything $7
3 anything $3
4 anything $50
4 anything $20
4 anything $10
5 anything $15
6 anything $15
6 anything $5
6 anything $5
Column "IdCostum" from "Customers" and "IdCostum" from "Invoices" are linked.
Coumn "Folio" from invoices and "Folio" from "InvoiceItems" are linked so the total of each Invoice is equal to the sum of
its items. (for example Invoice "1" is equal to $20, Invoice "2" is equal to $30, etc.)
I want to make a query that returns "IdCostum" and "Name" from "Customers" and the Total of Invoices of each customer like
this
Query
--------------------------------------
IdCostum Name Totals
--------------------------------------
1 John $35
2 Steve $105
3 Joe $40
You notice I need to make a sum of sum of columns
How can I make this query?
Thanks!
I'm using access and I have 3 tables: "Customers", "Invoices" and "InvoiceItems"
Invoices
------------------
Folio IdCostum
------------------
1 1
2 3
3 3
4 2
5 1
6 2
Customers
--------------------------------
IdCostum Name
--------------------------------
1 John
2 Steve
3 Joe
InvoiceItems
------------------------------
Folio Item Total
------------------------------
1 anything $10
1 anything $10
2 anything $30
3 anything $7
3 anything $3
4 anything $50
4 anything $20
4 anything $10
5 anything $15
6 anything $15
6 anything $5
6 anything $5
Column "IdCostum" from "Customers" and "IdCostum" from "Invoices" are linked.
Coumn "Folio" from invoices and "Folio" from "InvoiceItems" are linked so the total of each Invoice is equal to the sum of
its items. (for example Invoice "1" is equal to $20, Invoice "2" is equal to $30, etc.)
I want to make a query that returns "IdCostum" and "Name" from "Customers" and the Total of Invoices of each customer like
this
Query
--------------------------------------
IdCostum Name Totals
--------------------------------------
1 John $35
2 Steve $105
3 Joe $40
You notice I need to make a sum of sum of columns
How can I make this query?
Thanks!





RE: sum of sum of columns
select customers.idcostum, name, sum(total) as totals from customers
join invoices on customers.idcostum=invoices.idcostum
join invoiceitems on invoiceitems.folio=invoices.folio
group by customers.idcostum, name
RE: sum of sum of columns
SELECT Customers.IdCustom, name, sum(total) as Total_Sum
FROM (Customers INNER JOIN invoices ON Customers.IdCustom = invoices.IdCustom) INNER JOIN InvoiceItems ON invoices.Folio = InvoiceItems.Folio
group by Customers.IdCustom, name;
RE: sum of sum of columns