wood burning stoves*
The moose likes JDBC and the fly likes SQL beginner need help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL beginner need help" Watch "SQL beginner need help" New topic
Author

SQL beginner need help

Julien Martin
Ranch Hand

Joined: Apr 24, 2004
Posts: 384
Hello,

I have to deal with a single table which is as follows:



I want to end up with this:



Basically what I have done is a sum on the amounts of the same kind (d/c) and a group by on the client.

I have no idea on what SQL could produce the above result.

Can anyone help?

Thanks in advance,

Julien.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344

Have you tried executing the query? Just try it and see what it produces!


Everything has got its own deadline including one's EGO!
[CodeBarn] [Java Concepts-easily] [Corey's articles] [SCJP-SUN] [Servlet Examples] [Java Beginners FAQ] [Sun-Java Tutorials] [Java Coding Guidelines]
Julien Martin
Ranch Hand

Joined: Apr 24, 2004
Posts: 384
I haven't got a clue what the query should look like. Can you give me a hint please?
Julien.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344

try using inline views through which you can achieve.
Raghavan Muthu
Ranch Hand

Joined: Apr 20, 2006
Posts: 3344

Try getting the sum of debit, credit individually in the form of inline view and then try to include them in a single select (from respective inline views).
[ June 22, 2007: Message edited by: Raghavan Muthu ]
Edisandro Bessa
Ranch Hand

Joined: Jan 19, 2006
Posts: 584
The SQL below works for both Sybase and MySQL versions.

If you use another database just change it according to your needs.


[ June 22, 2007: Message edited by: Edisandro Bessa ]

"If someone asks you to do something you don't know how to, don't tell I don't know, tell I can learn instead." - Myself
Julien Martin
Ranch Hand

Joined: Apr 24, 2004
Posts: 384
Thanks Edisandro,

I adapted your query as follows so that it work with DB2:



which gives me this:





whereas I would like this:



Any idea what I am missing?

Julien.
Julien Martin
Ranch Hand

Joined: Apr 24, 2004
Posts: 384
I found the solution:

select sum(sum_debit) as tot_debit, sum(sum_credit) as tot_credit, client
from (select
(case debit_credit when 'd' then sum(amount) else 0 end ) as sum_debit,
(case debit_credit when 'c' then sum(amount) else 0 end ) as sum_credit,
client
from
test
group by client, debit_credit) as t
group by client
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL beginner need help
 
Similar Threads
Hibernate Doubt in HQL
Parameterized sql
learning Ant
why is this code not executing in java environment
algorithm sort ?