sum a column data based on another column data

  • 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.

  • 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';

  • 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