asked on

Query pitting one total against another

I am trying to write a query in oracle for sales vs received per day of the same item.

Basically I have two identical tables with the exception of one column - one tables shows sales per day, the other shows received per day.

I need to write a report showing the sales AND received per day on the same line, even if one or the other is zero.

How do I write this particular query?

slightwv (䄆 Netminder)

If you can provide some sample data and expected results, we can provide tested SQL.

Until then it sounds like a simple table join.

dbdp

ASKER

Something like the below. The query should return a zero value for Sales or Purchases if necessary like shown

Table 1 - Sales
Date      Item      Description1      Description2      Sales
01-Jan-1995      1      Fashion      Hat      133
01-Jan-1995      2      Fashion      Skirt      0
01-Jan-1995      3      Accessories      bracelet      60
01-Jan-1995      4      Accessories      necklace      9
02-Jan-1995      1      Fashion      Hat      135
02-Jan-1995      2      Fashion      Skirt      194
02-Jan-1995      3      Accessories      bracelet      13
02-Jan-1995      4      Accessories      necklace      0
03-Jan-1995      1      Fashion      Hat      113
03-Jan-1995      2      Fashion      Skirt      75
03-Jan-1995      3      Accessories      bracelet      31
03-Jan-1995      4      Accessories      necklace      191

Table 2 - Purchases
Date      Item      Description1      Description2      Purchases
01-Jan-1995      1      Fashion      Hat      164
01-Jan-1995      2      Fashion      Skirt      40
01-Jan-1995      3      Accessories      bracelet      116
01-Jan-1995      4      Accessories      necklace      0
02-Jan-1995      1      Fashion      Hat      199
02-Jan-1995      2      Fashion      Skirt      146
02-Jan-1995      3      Accessories      bracelet      134
02-Jan-1995      4      Accessories      necklace      67
03-Jan-1995      1      Fashion      Hat      103
03-Jan-1995      2      Fashion      Skirt      0
03-Jan-1995      3      Accessories      bracelet      41
03-Jan-1995      4      Accessories      necklace      5

Expected Result
Date      Item      Description1      Description2      Sales      Purchases
01-Jan-1995      1      Fashion      Hat      133      164
01-Jan-1995      2      Fashion      Skirt      0      40
01-Jan-1995      3      Accessories      bracelet      60      116
01-Jan-1995      4      Accessories      necklace      9      0
02-Jan-1995      1      Fashion      Hat      135      199
02-Jan-1995      2      Fashion      Skirt      194      146
02-Jan-1995      3      Accessories      bracelet      13      134
02-Jan-1995      4      Accessories      necklace      0      67
03-Jan-1995      1      Fashion      Hat      113      103
03-Jan-1995      2      Fashion      Skirt      75      0
03-Jan-1995      3      Accessories      bracelet      31      41
03-Jan-1995      4      Accessories      necklace      191      5

Sujith

You need to full outer join on a unique identifier on both the tables. But logically; you cant have a sale without purchase? if that is the case you only need a left outer join from the purchase table to sales table.

Something like this -

SQL> select * from tbl_receive;

      ITEM RECEIVE_AMT
---------- -----------
       400          40
       200          25

SQL>
SQL> select * from tbl_sales;

      ITEM  SALES_AMT
---------- ----------
       100         10
       200         20

SQL>
SQL> select  tr.item, ts.item, tr.receive_amt, ts.sales_amt
  2  from    tbl_receive tr full outer join tbl_sales ts on tr.item = ts.item
  3  ;

      ITEM       ITEM RECEIVE_AMT  SALES_AMT
---------- ---------- ----------- ----------
                  100                     10
       200        200          25         20
       400                     40

Open in new window

Modify the join to include all the key columns like Item Description1 Description2

dbdp

ASKER

Hi Sujith,
The problem with that is I do NOT want columns repeated - I only want one column Item number for example, and yes, you can have a sale on a day without a purchase on that day.

slightwv (䄆 Netminder)

>>you can have a sale on a day without a purchase on that day.

What do you want the data to look like then?

Here is a simple join example based on your expected results. It joins on the date,item and two description fields.

It produces your expected results.

Update your sample data and expected results, and we can update the SQL.

select  s.mydate, s.item, s.description1, s.description2, s.sales, p.purchases
from sales s
	join purchases p on s.mydate=p.mydate and  s.item=p.item and  s.description1=p.description1 and  s.description2=p.description2
/

Open in new window

dbdp

ASKER

Hi slightwv (䄆 Netminder)

But take item 2 on January 3rd , it has no purchases but I would want to see

03-Jan-1995 2 Fashion Skirt 75 0

similarly for item 2 on 1st January I would expect to see

01-Jan-1995 2 Fashion Skirt 0 40

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>it has no purchases but I would want to see

If the row exists in the purchases table with a 0, it will be included. No need for a full outer join.

The SQL I posted produces your expected results.

Now, if what Sujith is thinking that the row for the date/item isn't in the purchases or sales table, then yes, you need the full outer join.

As long as the rows are 1-to-1, a normal join works.

dbdp

ASKER

Does the trick, thanks

Sujith

You are welcome.