i am stuck in problem like i am passing accountID and on the basis of that SP picks amount details of a person e.g.
AccountID AccountTitle TransactionDate Amount
1 John01 2014/11/28 20
now if there is 2nd or more records for same accountID then it should add with previous e.g. if 2nd record for accountID 1 is 40 then amount should display 60 (such that it should be already added to 20 and display total in 2nd record)
AccountID AccountTitle TransactionDate Amount
1 John01 2014/12/30 60 (in real it was 40 but it should show result after being added to 1st record)
and same goes for further records
Select Payments.Accounts.AccountID, Payments.Accounts.AccountTitle,
Payments.Transactions.DateTime as TranasactionDateTime,
Payments.Transactions.Amount from Payments.Accounts
Inner Join Payments.Accounts
ON Payments.Accounts.AccountID = Payments.Transactions.Account_ID
Inner Join Payments.Transactions
where Payments.Transactions.Account_ID = 1
it has wasted my time and can't tackle it anymore, so please help me,
SQL Server 2012+ supports cumulative sums (which seems to be what you want):
Select a.AccountID, a.AccountTitle, t.DateTime as TranasactionDateTime,
t.Amount,
sum(t.Amount) over (partition by t.Account_Id order by t.DateTime) as RunningAmount
from Payments.Accounts a Inner Join
Payments.Transactions t
on a.AccountID = t.Account_ID
where t.Account_ID = 1;
In earlier versions of SQL Server you can most easily do this with a correlated subquery or using cross apply.
I also fixed your query. I don't know why you were joining to the Accounts table twice. Also, table aliases make queries much easier to write and to read.
Here is the answer if grouping by all columns is acceptable to you.
Select AccountID, AccountTitle, TransactionDate, SUM(Payments.Transactions.Amount)
from Payments.Accounts
group by AccountID, AccountTitle, TransactionDate
If you want to group only by AccountId, The query is this:
Select AccountID, SUM(Payments.Transactions.Amount)
from Payments.Accounts
group by AccountID
In the second query, the AccountTitle and TransactionDate are missing because they are not used in the group by clause. To include them in the results, you must think of a rule to decide which row of the multiple rows with the same AccountID is used to get the values AccountTitle and TransactionDate.
What version of SQL-Server are you using? This should do the trick:
Select AccountID, AccountTitle, TransactionData,
SUM(Amount) OVER (partiton by AccountID order by TransactionDate) .
from yourtable group by AccountID, AccountTitle, TransactionData
You take group of rows with AccountID, order them by Transaction date and count SUM in that group by Transaction date .
