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?
dbdpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If you can provide some sample data and expected results, we can provide tested SQL.

Until then it sounds like a simple table join.
0
dbdpAuthor Commented:
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
0
SujithData ArchitectCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dbdpAuthor Commented:
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.
0
slightwv (䄆 Netminder) Commented:
>>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

0
dbdpAuthor Commented:
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
0
SujithData ArchitectCommented:
Try this -

From the looks of your data I have assumed that date and Item are the keys

SQL> select * from sales;

DT              ITEM DESCRIPTION1         DESCRIPTION2              SALES
--------- ---------- -------------------- -------------------- ----------
01-JAN-95          1 Fashion              Hat                         133
01-JAN-95          2 Fashion              Skirt                         0
01-JAN-95          3 Accessories          bracelet                     60
01-JAN-95          4 Accessories          necklace                      9
02-JAN-95          1 Fashion              Hat                         135
02-JAN-95          2 Fashion              Skirt                       194
02-JAN-95          3 Accessories          bracelet                     13
02-JAN-95          4 Accessories          necklace                      0
03-JAN-95          1 Fashion              Hat                         113
03-JAN-95          2 Fashion              Skirt                        75
03-JAN-95          3 Accessories          bracelet                     31
03-JAN-95          4 Accessories          necklace                    191

12 rows selected.

SQL>
SQL> select * from purchases;

DT              ITEM DESCRIPTION1         DESCRIPTION2          PURCHASES
--------- ---------- -------------------- -------------------- ----------
01-JAN-95          1 Fashion              Hat                         164
01-JAN-95          2 Fashion              Skirt                        40
01-JAN-95          3 Accessories          bracelet                    116
01-JAN-95          4 Accessories          necklace                      0
02-JAN-95          1 Fashion              Hat                         199
02-JAN-95          2 Fashion              Skirt                       146
02-JAN-95          3 Accessories          bracelet                    134
02-JAN-95          4 Accessories          necklace                     67
03-JAN-95          1 Fashion              Hat                         103
03-JAN-95          2 Fashion              Skirt                         0
03-JAN-95          3 Accessories          bracelet                     41
03-JAN-95          4 Accessories          necklace                      5

12 rows selected.

SQL>
SQL> select  NVL(l.dt, p.dt) dt, NVL(l.item, p.item) item,
  2          NVL(l.description1, p.description1) description1,
  3          NVL(l.description2,p.description2) description2,
  4          NVL(sales,0) sales, NVL(Purchases,0) Purchases
  5  from    sales l full outer join purchases p on l.dt = p.dt and l.item = p.item;

DT              ITEM DESCRIPTION1         DESCRIPTION2              SALES  PURCHASES
--------- ---------- -------------------- -------------------- ---------- ----------
01-JAN-95          1 Fashion              Hat                         133        164
01-JAN-95          2 Fashion              Skirt                         0         40
01-JAN-95          3 Accessories          bracelet                     60        116
01-JAN-95          4 Accessories          necklace                      9          0
02-JAN-95          1 Fashion              Hat                         135        199
02-JAN-95          2 Fashion              Skirt                       194        146
02-JAN-95          3 Accessories          bracelet                     13        134
02-JAN-95          4 Accessories          necklace                      0         67
03-JAN-95          1 Fashion              Hat                         113        103
03-JAN-95          2 Fashion              Skirt                        75          0
03-JAN-95          3 Accessories          bracelet                     31         41
03-JAN-95          4 Accessories          necklace                    191          5

12 rows selected.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
>>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.
0
dbdpAuthor Commented:
Does the trick, thanks
0
SujithData ArchitectCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.