I want the sum of two columns from two different tables -- one column from each table. When I write a simple query like...
SELECT p.ddate , SUM (p.ddblock) , SUM (l.gtblock) FROM permit p , localpwblk l GROUP BY p.ddate;
... I received a wrong answer. (The sum from table p is multiplied by the number of records in table l and vice versa.)
I created some sample data; four rows in table "permit" and two rows in "localpwblk".
When you run a query with an aggregate function (like "SUM base_column_x)"), SQL first runs the corresponding query that includes each base_column. So in your example, SQL first runs this query:
SELECT p.ddate , p.ddblock , l.gtblock FROM permit p , localpwblk l; DDATE DDBLOCK GTBLOCK ----------- ---------- ---------- 21-Mar-2004 10 300 22-Mar-2004 20 300 22-Mar-2004 30 300 22-Mar-2004 40 300 21-Mar-2004 10 400 22-Mar-2004 20 400 22-Mar-2004 30 400 22-Mar-2004 40 400
Since you didn't specify any join condition between the two tables, SQL does a cross-join; that is, every row in each table is joined to every row in the other. SQL then computes the aggregates you requested, for the groups you specified, from that result set.
That explains what SQL is doing. How do you get SQL to do what you want? That depends on what you want, which you never mentioned. Apparently, you want a subtotal for each ddate from "permit", and something from "localpwblk". I'll make two guesses what that something is:
- The grand total: That is, you want the same figure from "localpwblk" repeated on each line of output.
- A correlated sub-total: That is, the figure from "localpwblk" may vary for each group. For example, you may want a daily total for the same day.
In either case, the elegant solution involves an in-line view, that is, a result set from a sub-query that is itself queried like a table. In-line views (new in Oracle 8) make dealing with GROUP BY and joins much, much easier. Whenever you have a problem with a GROUP BY and two or more tables, you should immediately think of in-line views.
If you are using Oracle 9 (or above), you can also use a scalar sub-query, a sub-query that returns one column and (at most) one row, that behaves like a columnin any other table.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.