Jump to content
Sign in to follow this  
Henry Olive

SQL

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

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
Guest

Please be a bit more verbose when you write your subject title. TIA!

Share this post


Link to post

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
Sign in to follow this  

×