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

This year's versus last year's sales, Part II

I am having trouble with a query. There are two tables from which I need to extract data. There are 57 columns...

in a table called "sales" and 60 columns in table called "orders". I will briefly give the details of the table:

ORDERS                                        
-------- 
order No(char 8)                             
catalog code(char 3)                        
total-sales(number 11)                     
net-sales(Number 11) 
  
SALES 
-------                                       
order No(char 8) 
division code(char 2) 
date(char 8)(yyyymmdd)

These above columns are the ones we use in the query.

We need the information for each catalog code, the sum of total sales, net sales, and the number of orders in a particular month of a year and the same data for the same month of the previous year. The user enters "division code" & "date"(user enters only month & year)

I tried a query but it is not working. The query is

select a.catalogcode,b.catalogcode,sum(a.total-sales),
sum(b.total-sales),sum(a.net-sales),sum(b.net-sales) 
from orders a,orders b where a.orderNo in(select orderNo 
from sales where divisioncode='60' and substr(date,1,6)='200103')
or b.orderNo in(select orderNo from sales where divisioncode='60' 
and substr(date,1,6)='200003') group by a.catalogcode,
b.catalogcode

If you can help me out in this regard I will be very thankful to you!


...continued from Part I

Do you see the problems? There are two.

First, perhaps we should consider using a full outer join. It might be important to include catalog codes which had some sales in March 2001 but none in March 2000, or vice versa. But for the sake of simplicity, let's assume we are interested only in catalog codes which have sales in both years. An ordinary inner join eliminates catalog codes having sales in only one year or the other but not both, and for now, that will do.

Also, we cannot really join on catalog code at all! Refer to the sample order data above, and see what happens with orders for catalog code ABC -- the above query would return 22,000 for March 2001 total sales, and 10,000 for March 2000 total sales! This is because order 22222 is joined with both orders 77777 and 88888, and order 44444 is also joined with both orders 77777 and 88888.

Clearly, another solution is required. We cannot use a cross product on order numbers. We need a query that produces totals by month that are then joined by catalog code. One way to do this is with views.

But first, a couple of words about column names. Do not use hyphens in a column name -- some databases will not even let you declare them. As well, do not use a reserved word such as date as a column name -- you're only asking for trouble.

So, using slightly altered column names, here are the views we will need --

create view march2001sales 
       ( catalogcode
       , totalsales
       , netsales
       , numberorders )
 as
  select catalogcode 
       , sum(totalsales)
       , sum(netsales)
       , count(*)
    from orders
       , sales 
   where orders.orderNo = sales.orderNo
     and sales.divisioncode='60' 
     and substr(salesdate,1,6)='200103'
group by catalogcode

create view march2000sales ( catalogcode , totalsales , netsales , numberorders ) as select catalogcode , sum(totalsales) , sum(netsales) , count(*) from orders , sales where orders.orderNo = sales.orderNo and sales.divisioncode='60' and substr(salesdate,1,6)='200003' group by catalogcode

Note I've added COUNT(*) in the views to obtain the number of orders. Here's the final query that you need --

  select march2001sales.catalogcode 
       , march2001sales.totalsales
       , march2001sales.netsales
       , march2001sales.numberorders
       , march2000sales.totalsales
       , march2000sales.netsales
       , march2000sales.numberorders
    from march2001sales
       , march2000sales
   where march2001sales.catalogcode = march2000sales.catalogcode

For More Information

  • What do you think about this answer? E-mail the Edtior at tdichiara@techtarget.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was last published in November 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close