Post reply

Sum of column from multiplied columns

  • August 26, 2020 at 6:48 pm

    #3783878

    I would like to get the sum of "Transaction Cost" as created from the multiplication of TxQty and AvgCostOld.

    Any help would be appreciated.

    SELECT  Item, TxQty, AvgCostOld,  TxQty*AvgCostOld AS TransactionCost,  TxNumber,  TxDate

    FROM     tblimInvTxHistory

    WHERE  TxDate > '2020-08-25'

    AND         TxCode = 'ADJ'

  • August 26, 2020 at 6:52 pm

    #3783881

    If you are going to SUM over a collection of rows, you need to define the grouping to SUM over.

    All rows? By item? By date?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 26, 2020 at 6:58 pm

    #3783885

    If possible I'd like to get just the sum of this row.  See attachment

     

     

    Attachments:
    You must be logged in to view attached files.

  • August 26, 2020 at 7:00 pm

    #3783891

    Like this?

    SELECT  SUM(TxQty*AvgCostOld) AS TotalTransactionCost
    FROM     tblimInvTxHistory
    WHERE  TxDate > '2020-08-25'
    AND         TxCode = 'ADJ'

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 26, 2020 at 7:00 pm

    #3783894

    PS, that's not a row, it's a column 🙂

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 26, 2020 at 7:11 pm

    #3783898

    Yes!  That will do nicely.  Thank you very much for your time.

  • August 26, 2020 at 7:16 pm

    #3783901

    nbeason wrote:

    Yes!  That will do nicely.  Thank you very much for your time.

    No problem, thanks for posting back.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • August 28, 2020 at 4:42 am

    This was removed by the editor as SPAM

  • August 28, 2020 at 1:15 pm

    #3784394

    Scroggins wrote:

    Select Math from the Formula Type drop down list;

    In the Choose a formula listbox, select SUMPRODUCT with criteria option;

    No such option is available from within SSMS, as far as I know.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply