Re: [sqlite] Join query help

[Mitchell Vincent]

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 coalesce(ctots.balance_due, 
0.00) as balance FROM customers as c left join (Select customer_id cid, 
sum(balance_due) balance_due FROM invoice_master group by cid) ctots on 
c.customer_id = ctots.cid ORDER by c.customer_name ASC

I need the sum() to be for the current customer ID coming from the other 
query..

So this :
(Select customer_id cid, sum(balance_due) balance_due FROM 
invoice_master group by cid)

Needs to become something like :
(Select customer_id cid, sum(balance_due) balance_due FROM 
invoice_master WHERE customer_id = c.customer_id group by cid)

But that doesn't work as I get a "no such column c.customer_id"
Kurt Welgehausen wrote:
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' substitute
'customers left join (subquery) on', and
for 'ctots.total' subsitute 'coalesce(ctots.total, 0.00)'.
select customers.*, coalesce(ctots.total, 0.00)
from customers left join
 (select customer_id cid, sum(invoice_amount) total
  from invoice_master group by cid) ctots
on customers.customer_id = ctots.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]

Re: [sqlite] Join query help

[Kurt Welgehausen]

>  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' substitute
'customers left join (subquery) on', and
for 'ctots.total' subsitute 'coalesce(ctots.total, 0.00)'.

select customers.*, coalesce(ctots.total, 0.00)
from customers left join
 (select customer_id cid, sum(invoice_amount) total
  from invoice_master group by cid) ctots
on customers.customer_id = ctots.cid

Regards

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Join query help

[Mitchell Vincent]

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 
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]

Re: [sqlite] Join query help

[Kurt Welgehausen]

> ...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]

[sqlite] Join query help

[Mitchell Vincent]

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 record..

Something like
SELECT *,sum(im.total) as total FROM customers AS c, invoice_master as i 
WHERE i.customer_id = c.customer_id GROUP BY c.customer_id;

Except that, of course, means that every customer has to have a record 
in the invoice table or they won't show in the list. I just want 0 to 
show for total for those customers that don't have a record in the 
invoice table.

Much appreciation up front as I know this is probably one of those "duh" 
kinds of questions..

-- Mitchell
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]