[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

Help with join Query

Can someone help me with the following, for some reason my sums are way off when i try to join by vendor


Query 1
select
  ship_date,
  vendor,
  sum(ord) as QTY
from wag
group by
ship_date,
vendor
ORDER BY VENDOR

RESULTS

Vendor		QTY
Pepsi		8
Coke		5
SevenUp		10

Open in new window



Query 2
select
vendor,
sum(ord) AS REC
from recinc
group by vendor



RESULTS

Vendor		REC
Pepsi		3
Coke		4
SevenUp		8

Open in new window


Results I am looking for

Vendor		QTY		REC
Pepsi		8		3
Coke		5		4
SevenUp		10		8

Open in new window

0
futureDBA
Asked:
futureDBA
  • 2
1 Solution
 
sdstuberCommented:
treat each query as an inline view and join the results


S ELECT   w.vendor, w.qty, r.rec
    FROM (SELECT   vendor, SUM(ord) AS qty
              FROM wag
          GROUP BY vendor) w,
         (SELECT   vendor, SUM(ord) AS rec
              FROM recinc
          GROUP BY vendor) r
   WHERE w.vendor = r.vendor
ORDER BY vendor
0
 
sdstuberCommented:
note,  the 1st query you posted did not generate the results you posted.

that query has ship_date in it but the results do not.  I removed that column in my answer above
0
 
futureDBAAuthor Commented:
thank you, worked

the end result of this query is going on an apex app.

ship_date will be a variable on a date picker, but this worked perfectly.. thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now