CODEWITHSUNDEEP
sqloracle-databaseselectgroup-bysum
marmar
marmar

Reputation: 113

Query compare from a table and a sum of multiple row in another table

I have a question regarding SQL query and I keep getting an error. So what im trying to do is getting a QTY value from a row in MASTER_DRAWING TABLE and compare it with the same name but with the sum of multiple occurences with the same key HEAD_MARK.

this is my SQL

SELECT MASTER_DRAWING.TOTAL_QTY AS TOTALQTY, 
       SUM(MASTER_DRAWING_ASSIGNED.ASSIGNED_QTY) AS SUMASSIGNEDQTY 
FROM MASTER_DRAWING, MASTER_DRAWING_ASSIGNED 
WHERE HEAD_MARK = 'TESTMULTIPLE' AND PROJECT_NAME = 'MILLHOUSE'

What I keep getting is 'ORA-00918: column ambiguously defined'

Am I doing something wrong here? in the end, I need to compare TOTALQTY with SUMASSIGNEDQTY for further operation

this is the tabe structure

MASTER_DRAWING,

  HEAD_MARK           VARCHAR2(20 CHAR),
  ENTRY_DATE          DATE,
  COMP_TYPE           VARCHAR2(20 CHAR),
  WEIGHT              NUMBER(11,2),
  SURFACE             NUMBER(15,3),
  PROFILE             VARCHAR2(20 CHAR),
  PROJECT_NAME        VARCHAR2(20 CHAR),
  LENGTH              NUMBER(15,2)              NOT NULL,
  TOTAL_QTY           NUMBER(38),
  SUBCONT_STATUS      VARCHAR2(20 CHAR),
  DISTRIBUTION_COUNT  NUMBER

and MASTER_DRAWING_ASSIGNED,

  ASSIGNED_WEIGHT    NUMBER(12,3),
  ASSIGNED_QTY       NUMBER(38),
  SURFACE            NUMBER(15,3),
  SUBCONT_ID         VARCHAR2(20 CHAR),
  REVISION_NO        NUMBER(2),
  ASSIGNMENT_DATE    DATE,
  PROJECT_NAME       VARCHAR2(20 CHAR),
  ID                 NUMBER(3),
  HEAD_MARK          VARCHAR2(20 CHAR),
  ASSIGNED_DUE_DATE  DATE,
  COMP_TYPE          VARCHAR2(25 CHAR),
  SIGNATURE          VARCHAR2(25 CHAR)

Upvotes: 1

Views: 111

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT MD.TOTAL_QTY AS TOTALQTY, MDA.SUMASSIGNEDQTY 
FROM MASTER_DRAWING MD 
INNER JOIN (SELECT MDA.HEAD_MARK, MDA.PROJECT_NAME, SUM(MDA.ASSIGNED_QTY) AS SUMASSIGNEDQTY 
            FROM MASTER_DRAWING_ASSIGNED MDA 
            GROUP BY MDA.HEAD_MARK, MDA.PROJECT_NAME
          ) MDA ON MD.HEAD_MARK = MDA.HEAD_MARK AND MD.PROJECT_NAME = MDA.PROJECT_NAME 
WHERE MD.HEAD_MARK = 'TESTMULTIPLE' AND MD.PROJECT_NAME = 'MILLHOUSE';

Upvotes: 1

Related Questions