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 firstname.lastname@example.org 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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.