Sum in a SQL Query/ View

Hi There I have a SQL Statement which i am using to create a View.

Select Amount ,TransactionType ,
case when Transactiontype = 'Collected' then amount else null end As Collected,
case when Transactiontype = 'WRITTENOFF' then amount else null end As WrittenOff
from Transactions

I want to add the two fields that I have created.
To look like this


Select Amount ,TransactionType ,
case when Transactiontype = 'Collected' then amount else null end As Collected,
case when Transactiontype = 'WRITTENOFF' then amount else null end As WrittenOff,
SUM(Collected+WrittenOff) as Total
from Transactions

But it keeps telling me that Column Does not exist.

Stanton_RouxAsked:
Who is Participating?
 
TimCotteeHead of Software ServicesCommented:
sql doesn't recognise those columns because they aren't really there. You have to code it the same again I am afraid:


Select Amount ,TransactionType ,
case when Transactiontype = 'Collected' then amount else null end As Collected,
case when Transactiontype = 'WRITTENOFF' then amount else null end As WrittenOff,
SUM(Case When TransactionType In ('Collected','WRITTENOFF') Then amount Else 0 End) as Total
from Transactions

And also make sure that you use 0 instead of Null in this part because adding NULL to any value results in NULL anyway so you wouldn't get the result you expect.
0
 
knightEknightCommented:
Select Amount ,TransactionType ,
case when Transactiontype = 'Collected' then amount else null end As Collected,
case when Transactiontype = 'WRITTENOFF' then amount else null end As WrittenOff,
case when Transactiontype in ('Collected','WRITTENOFF') then amount else null end As Total
from Transactions
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Stanton_Roux,


SELECT Amount, TransactionType, SUM(Collected+WrittenOff) as Total
from (
Select Amount ,TransactionType ,
case when Transactiontype = 'Collected' then amount else null end As Collected,
case when Transactiontype = 'WRITTENOFF' then amount else null end As WrittenOff
from Transactions ) a
GROUP BY Amount, TransactionType




Aneesh R
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Ooops

SELECT Amount, TransactionType, (Collected+WrittenOff) as Total
from (
Select Amount ,TransactionType ,
case when Transactiontype = 'Collected' then amount else null end As Collected,
case when Transactiontype = 'WRITTENOFF' then amount else null end As WrittenOff
from Transactions ) a
GROUP BY Amount, TransactionType



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.