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

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

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!


The first thing I always do in attacking a query problem is to rewrite it with pleasant indentation. Maybe it's just me, but I get lost easily unless I can quickly pinpoint the various clauses of the query.

  
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

The first thing I notice is that there is no join condition.

Whenever you join two tables, you need to qualify which rows you want from the so-called cross product of rows that you get as a result of the join. This is true even if, as in this case, they are two copies of the same table.

A cross product is what you get by matching each row of the first table with every row of the second table. In most cases, there's a key field in each table, and the key fields of the tables are usually compared in the join condition. In this example, you want to match the catalog code for last year's sales with the catalog code for this year's sales -- it just doesn't make sense to compare last year's sales in catalog code 27 with this year's sales in any other catalog code besides 27. If there are 100 catalog codes, there are a possible 100?*?100?=?10,000 different combinations of catalog codes, but you're only interested in the rows where last year's sales and this year's sales are for the same catalog code.

Secondly, you have a problem with the OR condition. The first part of the WHERE clause selects orders in division 60 for March 2001. The second part of the WHERE clause selects orders in division 60 for March 2000. I am quite certain that you want an AND condition. Unfortunately, since you're using OR, only one of the two WHERE clause conditions need be true in order for the entire WHERE clause to evaluate true. In other words, you get orders in division 60 for March 2001 matched with all other orders, and you get all other orders matched with orders in division 60 for March 2000. This is not a complete cross product, but still way more rows than you wanted.

We're still not out of the woods. It isn't enough that the catalog codes be the same, because if we're not careful, we'll still end up with a (portion of) a cross product.

Consider the following sample order data --

---- division 60 ----        ---- division 60 ----
----  March 2001 ----        ----  March 2000 ----

order  catalog   total       order  catalog   total
  no     code    sales         no     code    sales

22222    ABC     5,000       77777    ABC     3,000
33333    XYZ     1,000       88888    ABC     2,000
44444    ABC     6,000

Continued in Part II...


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.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close