CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008sum
Dinesh Kumar
Dinesh Kumar

Reputation: 101

Get one day and monthly sum of amount from one table in sql server

I want to write the query to calculate the amount as per current day and monthly.

I have TransactionID, Date, Amount columns in my Charge table.

I want it in this format:

currentDayAmount              TotalMonthlyAmount
$12305                        $236589                  --suppose on 31 Aug 2011
$62598                        $365489                  --suppose on 30 Sep 2011

Here CurrentDayAmount is which date I entered to excute the query and TotalMonthlyAmount is which month I entered the date. It means I want to execute with three dates. First is for the day which I want to run the query and last two is range of dates which is needs to total monthly amount. I execute the below query but shows the correct data but shows in rows.

select sum(c.amount) 
from charge c 
where c.eDate between '09/16/2011' and '09/17/2011'
union all 
select sum(c1.amount) 
from charge c1
where c1.eDate between '09/01/2011' and '09/20/2011'

Result

$33256.25
$705826.36

I want this data in columns as

current            Monthly
$33256.25          $705826.36

I hope this information is enough to understand u what I want to do.
Please give any suggestion or query to resolve my issue.

Thanks in advance

Upvotes: 0

Views: 526

Answers (2)

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

A slightly different approach:

SELECT SUM(CASE 
             WHEN c.edate BETWEEN '09/16/2011' AND '09/17/2011' THEN c.amount 
             ELSE 0 
           END), 
       SUM(CASE 
             WHEN c.edate BETWEEN '09/01/2011' AND '09/20/2011' THEN c.amount 
             ELSE 0 
           END), 
FROM   charge c

Upvotes: 1

jon
jon

Reputation: 408

Try this:

SELECT
    currentDayAmount = (
                         SELECT
                            SUM(c.amount)
                         FROM
                            charge c
                         WHERE
                            c.eDate BETWEEN '09/16/2011' AND '09/17/2011'
                       )
  , TotalMonthlyAmount = (
                           SELECT
                            SUM(c1.amount)
                           FROM
                            charge c1
                           WHERE
                            c1.eDate BETWEEN '09/01/2011' AND '09/20/2011'
                         )

Upvotes: 0

Related Questions