SQL Help on Join
Hello All, I am not getting the results that I am looking for may query. Here is my sql Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id The client_debt table is a list of account numbers that have an original balance and a current balance, then the client_pmt table has payments that were made to these accounts.The issue is that when a person makes more that one payment for a particular account, this causes my totals from my first table to rise and the number of records in the count as well. How is this supposed to be done? Thanks Mike [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Help on Join
Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Help on Join
how do I specify the tablename with the Count(*)? - Original Message - From: Scott Weikert To: CF-Talk Sent: Monday, December 01, 2003 1:37 PM Subject: Re: SQL Help on Join Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Help on Join
Hey Scott, I am still getting the two records for an account, if there are the records in the client_pmt table,How does distinct work? - Original Message - From: Scott Weikert To: CF-Talk Sent: Monday, December 01, 2003 1:37 PM Subject: Re: SQL Help on Join Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: SQL Help on Join
Typically you don't count * But you would do it this way... count(tablename.*) -Novak -Original Message- From: Mickael [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 10:18 AM To: CF-Talk Subject: Re: SQL Help on Join how do I specify the tablename with the Count(*)? - Original Message - From: Scott Weikert To: CF-Talk Sent: Monday, December 01, 2003 1:37 PM Subject: Re: SQL Help on Join Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Help on Join
Mickael, You probably want to do a sub-query to get the payments. I can never remember the syntax for that when you're using the left outer join syntax. In Oracle, it would be something like this: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, sub.totalpayment from Client_debt, (SELECT debt_id, Sum(Pmt_amt) as SUMPmtamt from client_pmt group by debt_id) AS sub WHERE client_debt.debt_id = sub.debt_id (+) - Original Message - From: Scott Weikert [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, December 01, 2003 12:37 PM Subject: Re: SQL Help on Join Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Help on Join
Hi Deanna I guess I would have to do a sub-queryI always heard that a sub-query was not very efficient and that doing a join was always better. Mike - Original Message - From: Deanna Schneider To: CF-Talk Sent: Monday, December 01, 2003 1:51 PM Subject: Re: SQL Help on Join Mickael, You probably want to do a sub-query to get the payments. I can never remember the syntax for that when you're using the left outer join syntax. In Oracle, it would be something like this: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, sub.totalpayment from Client_debt, (SELECT debt_id, Sum(Pmt_amt) as SUMPmtamt from client_pmt group by debt_id) AS sub WHERE client_debt.debt_id = sub.debt_id (+) - Original Message - From: Scott Weikert [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, December 01, 2003 12:37 PM Subject: Re: SQL Help on Join Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Help on Join
Yes, that's true. But, sub-queries are there for a reason - you can use them when you need to. - Original Message - From: Mickael [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, December 01, 2003 2:07 PM Subject: Re: SQL Help on Join Hi Deanna I guess I would have to do a sub-queryI always heard that a sub-query was not very efficient and that doing a join was always better. Mike - Original Message - From: Deanna Schneider To: CF-Talk Sent: Monday, December 01, 2003 1:51 PM Subject: Re: SQL Help on Join Mickael, You probably want to do a sub-query to get the payments. I can never remember the syntax for that when you're using the left outer join syntax. In Oracle, it would be something like this: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, sub.totalpayment from Client_debt, (SELECT debt_id, Sum(Pmt_amt) as SUMPmtamt from client_pmt group by debt_id) AS sub WHERE client_debt.debt_id = sub.debt_id (+) - Original Message - From: Scott Weikert [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Monday, December 01, 2003 12:37 PM Subject: Re: SQL Help on Join Mickael writes: Select Count(*) as TotalAccounts, Sum(Orig_amt) as SumOrig_AMT, Sum(Cur_Bal) as SumCurBal, Sum(Pmt_amt) as SUMPmtamt from Client_debt Left outer join Client_pmt on client_debt.debt_id = client_pmt.debt_id You're specifying the table names of the fields in your JOIN but not in your SELECT. Should be, for example, SUM(tablename.Orig_atm), etc. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
