CODEWITHSUNDEEP
sql-serversum
ullevi83
ullevi83

Reputation: 199

Sum table column from previous select results

I have 3 tables 

ITEMS(LOCNUMBER, CODE)
LOCATIONS(LOCNUMBER)
PLANT(CODE, UNITS)

I select all results based on location number (see below)

    select DISTINCT l.LOCNUMBER, i.CODE 
    from Locations l 
    inner join ITEMDETAILS i 
      on l.LOCNUMBER = i.LOCNUMBER 
    where l.LOCNUMBER= '4577';

Which gives me the following, but I now need to query the PLANT table and SUM(UNITS) based on each of the CODE returned from the query? How can this be done?

    LOCNUMBER   CODE
    4577        C44A
    4577        GE-AOR
    4577        GE-ARH
    4577        GE-ARP
    4577        GE-ARV
    4577        GE-BP
    4577        GE-EC
    4577        GE-EES
    4577        GE-JB
    4577        GE-JT
    4577        GE-JTR
    4577        GE-VL2
    4577        GE-VL4
    4577        L01A

Upvotes: 0

Views: 49

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

select l.LOCNUMBER, i.CODE,SUM(Units) 
    from Locations l 
    inner join ITEMDETAILS i 
      on l.LOCNUMBER = i.LOCNUMBER 
    inner join plant p
      on p.code=i.code
    where l.LOCNUMBER= '4577'
    GROUP BY  l.LOCNUMBER, i.CODE

Upvotes: 1

Related Questions