CODEWITHSUNDEEP
sqloracle-database
DuperDave
DuperDave

Reputation: 13

Oracle Statement to sum a calculated column by another column

I am trying to add all the extended prices for a company. Instead of my results being 

Company_1 2000
Company_1 2000
Company_1 1000
Company_2 2000
Company_2 1000

I would like to see

Company_1 5000
Company_2 3000

here is my current statement.

SELECT COMPANIES.COMPANY_NAME, 
       INVC_HEADER.INVOICE_DATE, 
       (INVC_DETAIL.QTY_SHIP * INVC_DETAIL.UNIT_PRICE) as Ext_Price
  FROM ( INVC_DETAIL INVC_DETAIL 
 INNER JOIN INVC_HEADER INVC_HEADER 
    ON INVC_DETAIL.INH_AUTO_KEY = INVC_HEADER.INH_AUTO_KEY ) 
 INNER JOIN COMPANIES COMPANIES 
    ON INVC_HEADER.CMP_AUTO_KEY=COMPANIES.CMP_AUTO_KEY
 ORDER BY COMPANIES.COMPANY_NAME, INVC_HEADER.INVOICE_DATE DESC

Upvotes: 1

Views: 243

Answers (1)

Tom
Tom

Reputation: 7740

You can do a SUM of the value you are looking for and GROUP BY Company Name

SELECT COMPANIES.COMPANY_NAME, 
        SUM(INVC_DETAIL.QTY_SHIP * INVC_DETAIL.UNIT_PRICE) as SumExt_Price
FROM   (INVC_DETAIL INVC_DETAIL INNER JOIN INVC_HEADER INVC_HEADER ON INVC_DETAIL.INH_AUTO_KEY=INVC_HEADER.INH_AUTO_KEY) 
        INNER JOIN COMPANIES COMPANIES ON INVC_HEADER.CMP_AUTO_KEY=COMPANIES.CMP_AUTO_KEY
GROUP BY COMPANIES.COMPANY_NAME

Upvotes: 3

Related Questions