Post reply

sum a column data based on another column data

  • August 28, 2014 at 12:08 pm

    #289556

    I am trying to sum a column total bases on an invoiced number column in tsql or ssrs.

    if I SUM(final) OVER(PARTITION BY InvoiceNmbr) it works fine on all the columns.

    But once I start to drill down or filter it by office or a user, I only get the total by each user on the invoice and not the cumulative total by the invoice.

    I want the cumulative total by invoice regardless of which filter I select.

    any help will be appreciated.

  • August 28, 2014 at 12:44 pm

    #1741726

    Can you share your complete query?

    It sounds like you are doing something like this:

    SELECT

    I.*,

    SUM(final) OVER(PARTITION BY InvoiceNmbr) AS invoicedTotal

    FROM

    dbo.invoices AS I

    WHERE

    I.USER = 'jsmith';

    But there are rows for an InvoiceNmbr by multiple users and when you run the query, you expect to see the total for the invoice number, while only showing details for the specific user. I think you need something like this:

    SELECT

    I.*,

    TI.invoicedTotal

    FROM

    dbo.invoices AS I CROSS APPLY

    (

    SELECT SUM(final) AS invoicedTotal FROM dbo.invoices AS TI WHERE I.InvoiceNmber = I2.InvoiceNmbr

    ) AS TI

    WHERE

    I.USER = 'jsmith';

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • August 28, 2014 at 12:51 pm

    #1741728

    Thanks Mr. Jack you are right on the money.

    That solved my issue.

Viewing 3 posts - 1 through 2 (of 2 total)

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