Problem solve Get help with specific problems with your technologies, process and projects.

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
    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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.