Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sum a column data based on another column data Expand / Collapse
Author
Message
Posted Thursday, August 28, 2014 12:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:15 AM
Points: 56, Visits: 240
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.
Post #1608368
Posted Thursday, August 28, 2014 12:44 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1608386
Posted Thursday, August 28, 2014 12:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:15 AM
Points: 56, Visits: 240
Thanks Mr. Jack you are right on the money.
That solved my issue.
Post #1608388
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse