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:
- What happens if there are sales in every month except for February 2001?
- What happens if there are sales in every month except for February 2001 and February 2002?
- Why is it a FULL OUTER JOIN?
- What are those COALESCE functions doing?
- 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.