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:
- 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.
This was first published in December 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation