Q

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

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close