Sql

[Henry Olive 5]

I wish everyone a healthy day
 

Interbase

Months Table

SMonth....LMonth

1...................January

2...................February

....

 

Invoice Table

ID.....CUSTNO...TNAME.....TOTAL......SMONTH

1.................20..........Buy.............5,000.................1

2.................20..........Buy.............8,000.................2

3.................20..........Sell...........10,000.................1

4.................20..........Sell.............5,000.................2

5.................20..........Sell.............6,000.................2

I'm trying to get below result for CustomerNo 20

LMONTH.......TOTALBUY....TOTALSELL

January................5,000............10,000

February..............8,000............11,000

 

I tried below sql with no success

SELECT M.LMONTH,
   Case When I.TNAME='Sell' then SUM(I.TOTAL) end as TOTALSELL ,
   Case When I.TNAME='Buy' then SUM(I.TOTAL) end as TOTALBUY
FROM INVOICE I
JOIN MONTHS M ON M.SMONTH=I.SMONTH
WHERE CUSTNO=20
GROUP BY M.LMONTH, I.TNAME

Can someone please help

Thank You

 

 

 

[Lajos Juhász 293]

I cannot test this but should be:

select M.LMoth, 

sum(Case When I.TNAME='Sell' then I.TOTAL else 0 end) as TOTALSELL ,
 sum(Case When I.TNAME='Buy' then I.TOTAL else 0  end) as TOTALBUY
FROM INVOICE I
JOIN MONTHS M ON M.SMONTH=I.SMONTH
WHERE CUSTNO=20
GROUP BY M.LMONTH

[Henry Olive 5]

Thank you so much Lajos

I get below result with your code

LMONTH.......TOTALBUY....TOTALSELL

January................5,000.........................0

January.........................0..............10,000

February..............8,000.........................0

February.......................0..............11,000

 

I want below result

LMONTH.......TOTALBUY....TOTALSELL

January................5,000............10,000

February..............8,000............11,000

 

 

[Stano 143]

Tento dotaz použite ako poddotaz pre svoj konečný dotaz. Použite SUM () a SKUPINU MESIACOM

[Lajos Juhász 293]

27 minutes ago, Henry Olive said:

I get below result with your code

LMONTH.......TOTALBUY....TOTALSELL

January................5,000.........................0

January.........................0..............10,000

I don't have Interbase. It's impossible that  "GROUP BY M.LMONTH" can produce this result. The only way you can get this if you left TName inside group by.

[Stano 143]

Pravda. Nevšimol som si GROUP BY M.LMONTH

[Henry Olive 5]

You are right, i added TNAME to see what will happen & i forgot it

Thank you so much