[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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.

0
Stanton_Roux
Asked:
Stanton_Roux
  • 2
3 Solutions
 
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
 
TimCotteeCommented:
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now