Q
Problem solve Get help with specific problems with your technologies, process and projects.

GROUP BY with two tables, part 3

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

Set-up script and sample data

This shows the sample data used above. Remember, Oracle DATEs always include a time-of-day. In permit.ddate, this time is always midnight. Use the TRUNC function to return a date with a time of midnight. The userid column is not used in the examples above; I included it to show that there could be several meaningful ways of combining totals from the two tables.

CREATE TABLE    permit
(   ddblock   NUMBER (8)
,   ddate     DATE
,   userid    NUMBER (6)
);

INSERT INTO  permit  VALUES (10, TRUNC (SYSDATE - 1), 1); 
INSERT INTO  permit  VALUES (20, TRUNC (SYSDATE),     1); 
INSERT INTO  permit  VALUES (30, TRUNC (SYSDATE),     2); 
INSERT INTO  permit  VALUES (40, TRUNC (SYSDATE),     3); 

SELECT  *
FROM    permit;

   DDBLOCK DDATE           USERID
---------- ----------- ----------
        10 21-Mar-2004          1
        20 22-Mar-2004          1
        30 22-Mar-2004          2
        40 22-Mar-2004          3

CREATE TABLE    localpwblk
(   gtblock   NUMBER (8)
,   ldate     DATE
,   userid    NUMBER (6)
);

INSERT INTO  localpwblk  VALUES (300, SYSDATE, 3);
INSERT INTO  localpwblk  VALUES (400, SYSDATE, 4);

SELECT  *
FROM    localpwblk;

   GTBLOCK LDATE           USERID
---------- ----------- ----------
       300 22-Mar-2004          3
       400 22-Mar-2004          4

Click to return to part 1.

Dig Deeper on Using Oracle PL-SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close