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.