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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: