Q

Crosstab totals by month

I have table like this:

account  descrip  transdate   amount
  11     service  10/12/2003  2000
  11     service  09/10/2003  2500
  12     parts    15/7/2003   1520

I want to group it by account and month like this:

account  jan  feb  mar ....
service  1500 2000 1750 
parts    1250 780  888

There was a similar question just last week: A "crosstab" query (27 October 2003). With a couple of these in the Ask The Expert archives, we may not answer similar questions in future. Getting totals by month across the row, however, is a very common denormalization or "crosstab" request, so let's do this one and hopefully the general strategy will be clear for similar questions in future.

The trick to writing a "crosstab" query is to utilize a CASE structure for each column, and a GROUP BY on the key. The drawback is that this query cannot be dynamic. You have to know in advance which columns you want. In the case of totals by month, however, there are only ever twelve months in a year (unless it's an accounting application, where there may be up to fifteen).

select descrip
             as Account
     , sum(
        case when month(transdate) = 01
             then amount else 0 end
           ) as Jan 
     , sum(
        case when month(transdate) = 02
             then amount else 0 end
           ) as Feb 
   ...
     , sum(
        case when month(transdate) = 12
             then amount else 0 end
           ) as Dec 
     , sum(amount)
             as Total
  from yourtable
group
    by descrip

Notice how you don't need to use a CASE for the total of all monthly columns across the row; just sum up all amounts.


This was first published in November 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close