Q

Sum of sales for two years, by month

I need a query that will give me the following result for a particular region. I need to get the sum of sales value for two years for one particular region, and group by months.

I have a sales table which consists of the following columns:

id (autogenerated)
Region
SalesDate
SalesAmount 

I need a query that will give me the following result for a particular region. I need to get the sum of sales value for two years for one particular region, and group by months as follows:

  
month   2000    2001
-----  ------  -----
Jan    111.0   332.00
Feb    222.0   225.00
March  232.0   234.00

I am able to get it for one year.

The query for one year would be:

select monthno(SalesDate) as mm
     , monthname(SalesDate) as mth
     , sum(SalesAmount) as amt
  from SalesTable
 where Region = 937
   and year(SalesDate) = 2001 
group
    by monthno(SalesDate) 
     , monthname(SalesDate) 

Please overlook the fact that monthno and monthname may not actually be functions in SQL. Just choose the equivalent functions in your particular database system.

Now combine the results for two years like this:

select coalesce(y1.mm,y2.mm) as "mm"
     , coalesce(y1.mth,y2.mth) as "month"
     , sum(y1.amt) as "2001"
     , sum(y2.amt) as "2002"
  from (
       select monthno(SalesDate) as mm
            , monthname(SalesDate) as mth
            , sum(SalesAmount) as amt
         from SalesTable
        where Region = 937
          and year(SalesDate) = 2001 
       group
           by monthno(SalesDate)
            , monthname(SalesDate)
       ) as y1
full outer
  join (                  
       select monthno(SalesDate) as mm
            , monthname(SalesDate) as mth
            , sum(SalesAmount) as amt
         from SalesTable
        where Region = 937
          and year(SalesDate) = 2002 
       group
           by monthno(SalesDate)
            , monthname(SalesDate)    
       ) as y2
    on y2.mth = y1.mth
order
    by coalesce(y1.mm,y2.mm)

Now let me to put to all of you good readers the following questions:

  1. What happens if there are sales in every month except for February 2001?
  2. What happens if there are sales in every month except for February 2001 and February 2002?
  3. Why is it a FULL OUTER JOIN?
  4. What are those COALESCE functions doing?
  5. Optional Is there an easier way to do this using the analytic functions in SQL-99?

Please submit your brief explanation and we'll publish the best one. Remember, keep it brief. You may prefer (or not) to have your name mentioned, so please make sure you give your preference. We would never publish your email in any case.

This was first published in December 2006

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