Sum up a field in SQL Statement

Posted on 2009-04-29
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

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 ?

Question by:AXISHK
    LVL 9

    Accepted Solution

    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

    LVL 2

    Expert Comment

    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>

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now