?
Solved

Sum up a field in SQL Statement

Posted on 2009-04-29
2
Medium Priority
?
205 Views
Last Modified: 2012-06-27
I have a table like this

account,   year,   period, amount
491         2008         1          100
491         2008          1           50
491         2008          1        -100
491         2008          1        -200

Result
account   year  period  DrAmt      CrAmt        
491         2008     1      150          -300

The amount is either +ve or -ve value. Is it possible to write a single SQL statement to display a summary of +ve value and -ve value in two separate calculation field ?

thank
0
Comment
Question by:AXISHK
2 Comments
 
LVL 9

Accepted Solution

by:
dan_neal earned 2000 total points
ID: 24266687
Have you tried:
Of course this is assuming that the amount field is of a numeric data type (i.e. int, decimal, float)

Select account, year, period, sum(case when amount > 0 then amount else 0 end) as DrAmt, sum(case when amount < 0 then amount else 0 end) as CrAmt from <your table name> group by account, year, period

Open in new window

0
 
LVL 2

Expert Comment

by:clinton_cunningham
ID: 24266735
select  account, sum(amount) as Total from <table name>
group by account

--group by might not be needed, if you get errors, try just
select  sum(amount) as Total from <table name>
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question