Home » SQL & PL/SQL » SQL & PL/SQL » sum of a column got from joining multiple tables (Oracle 10g, Win7)
sum of a column got from joining multiple tables [message #563570] Wed, 15 August 2012 02:54 Go to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
CREATE TABLE T1
      (
      T1_ID NUMBER PRIMARY KEY,
      NAME VARCHAR2(20) NOT NULL
      )

      CREATE TABLE T2
      (
      T2_ID NUMBER PRIMARY KEY,
      NAME VARCHAR2(50) NOT NULL,
      T1_ID NUMBER NOT NULL,
      CONSTRAINT T1_FK FOREIGN KEY (T1_ID)
      REFERENCES T1(T1_ID)
      )

      CREATE TABLE T3
      (
      T3_ID NUMBER PRIMARY KEY,
      PRICE FLOAT NOT NULL,
      INS_DATE date default sysdate,      
      T2_ID NUMBER NOT NULL,              
      CONSTRAINT T2_FK FOREIGN KEY (T2_ID)
      REFERENCES T2(T2_ID)
      )  
////////////////////////////
INSERT ALL
       INTO T1 (T1_ID,NAME) VALUES (1,'BEM')
       INTO T1 (T1_ID,NAME) VALUES (2,'KIN')
       INTO T1 (T1_ID,NAME) VALUES (3,'STURTS')       

       INTO T2 (T2_ID, NAME,T1_ID) VALUES(1,'VLES',2)
       INTO T2 (T2_ID, NAME,T1_ID) VALUES(2,'WAR',2)
       INTO T2 (T2_ID, NAME,T1_ID) VALUES(3,'RECD',1)
       INTO T2 (T2_ID, NAME,T1_ID) VALUES(4,'FARE',3)
       
        INTO T3 (T3_ID, PRICE,T2_ID) VALUES(1,22,1)
        INTO T3 (T3_ID, PRICE,T2_ID) VALUES(2,3,2)
        INTO T3 (T3_ID, PRICE,T2_ID) VALUES(3,1000,4)
		INTO T3 (T3_ID, PRICE,T2_ID) VALUES(4,86,3)
      SELECT * FROM DUAL;


I can calculate the sum of the price column from single table T3, like:
SELECT
      Nvl(T3_ID,'')AS id,Sum(price) AS price
FROM T3
      GROUP BY rollup(T3_ID);

But when joining the three tables, how to calculate the sum of price column:
SELECT 
        T1.NAME,
        T2.NAME,
        T3.PRICE,
        T3.INS_DATE
     FROM T1,T2,T3
     WHERE
        T1.T1_ID = T2.T1_ID
     AND
        T3.T2_ID = T2.T2_ID;


How can I get the sum of PRICE column from the above query??
Re: sum of a column got from joining multiple tables [message #563572 is a reply to message #563570] Wed, 15 August 2012 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 57606
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With words, what do you want as result? sum of price of what? Along with which columns?

Tom Kyte
I don't know why people just post things like:

<bunch of data with no explaination>

<my broken query that doesn't work here>

<output i didn't want here>

<expected output>

and expect us to perfectly reverse engineer all of their rules, assumptions, etc from it.


Here we even have not the expected output for the data!

Regards
Michel
Re: sum of a column got from joining multiple tables [message #563603 is a reply to message #563572] Wed, 15 August 2012 15:27 Go to previous message
Bill B
Messages: 1049
Registered: December 2004
Senior Member
SELECT 
        T1.NAME,
        T2.NAME,
        sum(T3.PRICE) price,
        T3.INS_DATE
     FROM T1,T2,T3
     WHERE
        T1.T1_ID = T2.T1_ID
     AND
        T3.T2_ID = T2.T2_ID
group by T1.NAME,T2.NAME,T3.INS_DATE;
Previous Topic: QUERY (2 Merged)
Next Topic: How does DBMS_SQL differ from Native Dynamic Code
Goto Forum:
  


Current Time: Thu Apr 17 08:50:51 CDT 2014

Total time taken to generate the page: 0.10977 seconds