• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • 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
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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