Really appreciate your help! However that query doesn't give correct
results (though it does give a row for every customer!!!)..
The problem is the sum() in the join isn't qualified against the
selected customer ID..
Using this :
SELECT c.customer_number as customer_number
> 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
You're right -- sorry, I wan't paying attention.
For 'customers, (subquery) where'
Very nice, however that still only gives me the customer records if they
have an invoice in the invoice_master table..
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
> ...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:
So I'm sitting here in a pinch and my brain just refuses to work...
2 tables, a customer and an invoice table. What is the proper SQL to get
all customers records, plus the sum of a column in the invoice table
with a relation on the customer ID, but not all customers might have an
invoice
5 matches
Mail list logo
