Jump to content
Sign in to follow this  
Henry Olive

Sql

Recommended Posts

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

 

 

 

Share this post


Link to post

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

Share this post


Link to post

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

 

 

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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

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  

×