Problem solve Get help with specific problems with your technologies, process and projects.

GROUP BY with two tables, part 1

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;

----------- ---------- ----------
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:

  1. The grand total: That is, you want the same figure from "localpwblk" repeated on each line of output.
  2. 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.

Click to continue in part 2.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.