Help with join Query

Posted on 2012-08-31
Last Modified: 2012-08-31
Can someone help me with the following, for some reason my sums are way off when i try to join by vendor

Query 1
  sum(ord) as QTY
from wag
group by


Vendor		QTY
Pepsi		8
Coke		5
SevenUp		10

Open in new window

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


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

Question by:futureDBA
    LVL 73

    Expert Comment

    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
    LVL 73

    Accepted Solution

    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

    Author Comment

    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

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now