Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL beginner need help

 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you tried executing the query? Just try it and see what it produces!
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I haven't got a clue what the query should look like. Can you give me a hint please?
Julien.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try using inline views through which you can achieve.
 
Raghavan Muthu
Ranch Hand
Posts: 3381
Mac MySQL Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 584
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Julien Martin
Ranch Hand
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 384
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic