Results 1 to 2 of 2

Thread: Sum

  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: Sum

    Folks

    I have Shares, AcctID, PropNum columns in Holding Table
    I have Price, PropNum columns in Assets Table
    I have AcctId , MarketValue columns in Account Table

    I need to find out this expression


    Account.MarketValue = Sum (holding.Shares * Assets.Price) for each and every account.


    I am not sure of using the SUM function. Can anyone help me out.

    I wrote the join query like this but not able to write the SUM function.


    SELECT dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc AS MarketValue
    FROM dbo.HOLDING INNER JOIN
    dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num

    I know that I have to use GROUP BY n Order BY but not sure of using how.

    Can you help me out.




    Thks

  2. #2
    Join Date
    Feb 2004
    Location
    Erehwon
    Posts
    18

    Re: Sum

    I'm assuming what you really want is to update the value of 'MarketValue' to Sum (holding.Shares * Assets.Price)

    I think this will do it:

    Code:
    update account set marketvalue= 
    
    (
    select sum(shares*price) 
    from holding left join assets 
    on holding.propnum=assets.propnum  
    where holding.acctid=account.acctid
    group by acctid
    );

    Bon chance!
    Ursus
    Originally posted by kir441
    Folks

    I have Shares, AcctID, PropNum columns in Holding Table
    I have Price, PropNum columns in Assets Table
    I have AcctId , MarketValue columns in Account Table

    I need to find out this expression


    Account.MarketValue = Sum (holding.Shares * Assets.Price) for each and every account.


    I am not sure of using the SUM function. Can anyone help me out.

    I wrote the join query like this but not able to write the SUM function.


    SELECT dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc AS MarketValue
    FROM dbo.HOLDING INNER JOIN
    dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num

    I know that I have to use GROUP BY n Order BY but not sure of using how.

    Can you help me out.




    Thks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •