GROUP BY with two tables, part 2

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.