A sub-select or outer join or something of the sort is needed but I can't get it to work..
In PostgreSQL I might do :
SELECT *,(SELECT sum(total) FROM invoice_master WHERE invoice_master.customer_id = customers.customer_id) as total FROM customers;
However I can't get that beast to work in SQLite.
Many thanks!
Kurt Welgehausen wrote:
...get all customers records, plus the sum of a column in the invoice...
The idea is to get customer_id and the sum from the invoice table, then join that with the rest of the customer info. Of course, if you want to do it in one SQL statement, you have to write those steps in reverse order:
select customers.*, ctots.total from customers, (select customer_id cid, sum(invoice_amount) total from invoice_master group by cid) ctots where customers.customer_id = ctots.cid
If this doesn't make sense to you, try using a temporary table:
create temp table ctots (cid integer primary key, total float) -- or whatever types are appropriate
insert into ctots select customer_id, sum(invoice_amount) from invoice_master group by customer_id
select customers.*, ctots.total from customers, ctots where customer_id = cid
Regards
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
