Re: SQL statements

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Wed, 4 Dec 2013 20:12:39 +0100
Message-ID: <CA+S=qd2UVwO43X9HQEh3X5hf8jw+Q7BbGC+s2bCyFsYMjTNT3Q_at_mail.gmail.com>

I would guess you are looking for an outer join, Nathan.

Something like:

select
c.id,
sum(t.debit) sumdebit,
sum(t.credit) sumcredit
from clients c
left outer join transactions t
on t.client_id = c_id
group by c.id;

Balance you can then get by subtracting credit for debit (I think, I am not economics expert ;-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Wed, Dec 4, 2013 at 8:01 PM, <Nathan.Dhital_at_wellsfargo.com> wrote:

> Hello Experts –
>
>
>
> I wonder if I can ask with you about the syntax of sql statement to get
> the right results for the following scenario:
>
>
>
> Scenario :
>
> Given the following two tables:
>
>
>
> *clients* (id (PK), name)
>
> transactions (id (PK), client_id, trans_date, debit, credit, comment)
>
> transactions.client_id references clients.id
>
> What is precise sql statements which selects the balance for ALL clients,
> even if they have no transactions. (Assume initial balances to be zeros).
>
> Sample table contents:
>
> *clients:*
>
> id name
>
> -- ------
>
> 1 Pit-bull
>
> 2 Jennifer
>
> 3 Casper
>
>
>
> transactions:
>
> id client_id trans_date debit credit comment
>
> -- --------- ---------- ------- ------- -----------------
>
> 1 | 1 06/16/2013 NULL 1000.00 SALARY
>
> 2 | 1 06/18/2013 516.43 0.00 MOB PHONE PAYMENT
>
> 3 | 2 06/16/2013 NULL 1000.00 SALARY
>
> 4 | 2 06/18/2013 200.00 NULL INTERNET PAYMENT
>
> 5 | 2 06/18/2013 500.00 0.00 MOB PHONE PAYMENT
>
>
>
>
>
> Sincerely,
>
>
>
>
>
> NathanT Dhital
>
> Sr. Database DBA
>
> Commbank-Internet
>
> HQ
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 04 2013 - 20:12:39 CET