CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Saifuddin
Saifuddin

Reputation: 99

Grand total of sum column and two different periods total

I know it may sound little bit odd, but i have two questions to ask

  1. grand total of sum() column - NetAmount
  2. to have two period data for comparison - sum of base_amount

my query read as below

select a.usage_id BillingGroup, a.usage_desc BGDesc, sum(a.base_amount) NetAmount
from cdi_item_dtl a, cdi_invoice_hdr b
where a.tran_type = 'RM_CMI'
and a.tran_no = b.tran_no
and b.tran_date between '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000'
group by a.usage_id, a.usage_desc
order by a.usage_id

which gives me total of all groups at NetAmount, i want to have grand total of NetAmount and second point as this query shows june 2014 period, is it possible to have NetAmount2013 as next column for june 2013 beside in single query?

Upvotes: 0

Views: 780

Answers (1)

AHiggins
AHiggins

Reputation: 7219

Quick and easy way to do it would be as follows: you should probably improve this by avoiding hardcoded strings and doing some comparisons against the MONTH() and YEAR() functions instead, but this will show you the basic idea.

select 
    a.usage_id BillingGroup, 
    a.usage_desc BGDesc, 
    sum(CASE WHEN b.tran_date between '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000' THEN a.base_amount ELSE 0 END) NetAmount2014,
    sum(CASE WHEN b.tran_date between '2013-06-01 00:00:00.000' and '2013-06-30 00:00:00.000' THEN a.base_amount ELSE 0 END) NetAmount2013
from cdi_item_dtl a, cdi_invoice_hdr b
where 
    a.tran_type = 'RM_CMI'
    and a.tran_no = b.tran_no
    and 
      (
        b.tran_date between '2014-06-01 00:00:00.000' and '2014-06-30 00:00:00.000'
        OR b.tran_date between '2013-06-01 00:00:00.000' and '2013-06-30 00:00:00.000'
      )
group by a.usage_id, a.usage_desc
order by a.usage_id

Upvotes: 1

Related Questions