Frank Cazabon (3/12/2013)
Hi,I have to create a report like this:
Deposit ID | Deposit Amount | Receipt # | Receipt Amount1 | 1000 | 1 | 500
| | 2 | 500
2 | 2000 | 3 | 1500
| | 4 | 500
------------------------------------------------------------
Total | 3000 | 1 | 3000
I've pulled all the data into one dataset and have the layout and totalling of the Receipt Amount fine.
My problem is that the total Deposit Amount is way over because it occurs once in each row, even though I am only displaying it once for each row. Is there a way that I can get this totalled properly?
Try getting your totals like this:
SELECT
SUM(DepositAmount) OVER (PARTITION BY DepositID) AS TotalDeposits
,SUM(ReceiptAmount) OVER (PARTITION BY ReceiptID) AS TotalReceipts