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.
