SQL

[Henry Olive 5]

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

 

 

 

 

[bazzer747 25]

Is there a question here?

[Henry Olive 5]

Thank you Bazzer747

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

from the table by SQL ?
 

[Lajos Juhász 293]

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 

[Guest]

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

[Stano 143]

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

Edit

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

Edited May 1, 2021 by Stano

[Henry Olive 5]

Thank you so much Lajos, Stano