Jump to content

Recommended Posts

I wish everyone a healthy day.

 

Interbase-2007

 

I have below table

ITEMNO.....TNAME......QTY...UPRICE

AAA...............Buy..........10.........5,00

AAA...............Buy..........20.........5,00

AAA...............Sell............4..........7,00

AAA...............Return......2..........7,00

BBB...............Buy............6..........6,00

BBB...............Sell.............3..........8,00

CCC...............Buy.........12........10,00

 

Requested Result by (QTY * UPRICE) for each item

ITEMNO.....TOTALBUY....TOTALSELL......TOTALRETURN

AAA...................150,00..........28,00................14,00

BBB......................36,00...........24,00..................0,00

CCC...................120,00...........0,00.....................0,00

 

Thank You

 

 

 

 

Share this post


Link to post

Thank you Bazzer747

Yes it is a question, How can i get requested result

from the table by SQL ?
 

Share this post


Link to post

I have no experience with Interbase but a general sql could be:

select itemno,  
       sum(case when tname='Buy' then qty*uprice else 0 end) as totalbuy,
       sum(case when tname='Sell' then qty*uprice else 0 end) as totalsell,
       sum(case when tname='Return' then qty*uprice else 0 end) as totalreturn
from <Your table>
group by 1 

Share this post


Link to post
Posted (edited)

Using case ... when ... then and the aggregation function SUM

Edit

It's in Lajos Juhász's post. I did not see it.

Edited by Stano

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×