Ask the Expert

GROUP BY with two tables, part 2

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.)

    Requires Free Membership to View

Solutions:
Solution 1: Uncorrelated GROUP BY
Solution 2: Correlated GROUP BY

Uncorrelated GROUP BY

This assumes the desired results consist of one row for every permit.ddate; each row also shows the grand total from localpwblk. In this solution, the relevant totals from each table are computed in in-line views.

SELECT    pg.ddate
,         pg.sum_ddblock
,         lg.sum_gtblock 
FROM      (  -- begin in-line view pg on permit
              SELECT    ddate
              ,         SUM (ddblock) AS sum_ddblock
              FROM      permit
              GROUP BY  ddate
          ) pg
,         (  -- begin in-line view lg on localpwblk
              SELECT  SUM (gtblock)  AS sum_gtblock
              FROM    localpwblk
          ) lg
ORDER BY  pg.ddate;

DDATE       SUM_DDBLOCK SUM_GTBLOCK
----------- ----------- -----------
21-Mar-2004          10         700
22-Mar-2004          90         700

Notice that there is no join condition, so the result is a cross-join. Since there is only one row in one of the "tables" (the in-line view "lg") this does not result in an abnormal number of rows in the final result set.

Starting with version 9.0 of Oracle, you can get exactly the same results with a scalar sub-query on "localpwblk".

SELECT    pg.ddate
,         pg.sum_ddblock
,         (  -- Begin scalar sub-query
              SELECT  SUM (gtblock)  AS sum_gtblock
              FROM    localpwblk
          ) AS sum_gtblock
FROM      (  -- begin in-line view pg on permit
              SELECT    ddate
              ,         SUM (ddblock) AS sum_ddblock
              FROM      permit
              GROUP BY  ddate
          ) pg
ORDER BY  pg.ddate;

Correlated GROUP BY

Here, the desired results are one row for each ddate. The total of localpwblk.gtblock for the same day, if any, is included.

SELECT    pg.ddate
,         pg.sum_ddblock
,         lg.sum_gtblock 
FROM      (  -- begin in-line view pg on permit
              SELECT    ddate
              ,         SUM (ddblock) AS sum_ddblock
              FROM      permit
              GROUP BY  ddate
          ) pg
LEFT OUTER JOIN
          (  -- begin in-line view lg on localpwblk
              SELECT    TRUNC (ldate)  AS tldate
              ,         SUM (gtblock)  AS sum_gtblock
              FROM      localpwblk
              GROUP BY  TRUNC (ldate)
     ) lg
ON        pg.ddate = lg.tldate
ORDER BY  pg.ddate;


DDATE       SUM_DDBLOCK SUM_GTBLOCK
----------- ----------- -----------
21-Mar-2004          10
22-Mar-2004          90         700

In Oracle 9.0 (and later) you can get exactly the same results with a scalar sub-query.

SELECT    pg.ddate
,         pg.sum_ddblock
,         (  -- Begin scalar sub-query
              SELECT  SUM (gtblock)  AS sum_gtblock
              FROM    localpwblk
              WHERE   TRUNC (ldate) = ddate
          ) AS sum_gtblock
FROM      (  -- begin in-line view pg on permit
              SELECT    ddate
              ,         SUM (ddblock) AS sum_ddblock
              FROM      permit
              GROUP BY  ddate
          ) pg
ORDER BY  pg.ddate;

Click to continue in part 3.

This was first published in March 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: