CODEWITHSUNDEEP
sql
Shahed Kaiser
Shahed Kaiser

Reputation: 1

How Can I Use Order By In Aggregate Function

I have a sql table seems like below

ID  AccountName Credit Debit CurrentBalance
1   John         10     0     10
2   Smith        20     0     20
3   Angel        0      10    20
4   John         30     0     40
5   Smith        0      10    10
6   Angel        30     0     50

Expected Result

AccountName TotalCredit TotalDebit CurrentBalance
John        40          0          40
Smith       20          10         10
Angel       30          10         50

I want the sum of credit and debit of every account and also the latest currentbalance of each account.

I have used this query

select AccountName, 
       SUM(CreditAmount) As CreditAmount, 
       SUM(DebitAmount) As DebitAmount, 
       MAX(CurrentBalance) As CurrentBalance 
from TransactionInfo 
GROUP BY AccountName;

Upvotes: 0

Views: 41

Answers (3)

mkRabbani
mkRabbani

Reputation: 16908

You can try this-

NOTE: Considering MAX(ID) for a user will be the latest.

SELECT AccountName, 
SUM(CreditAmount) As CreditAmount, 
SUM(DebitAmount) As DebitAmount, 
(   SELECT CurrentBalance 
    FROM TransactionInfo B 
    WHERE B.AccountName = A.AccountName
    AND B.ID = (SELECT MAX(C.ID) FROM TransactionInfo C WHERE C.AccountName = A.AccountName)
) AS CurrentBalance
FROM TransactionInfo A GROUP BY AccountName;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

If your database supports ROW_NUMBER, then here is one way to do this:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY ID DESC) rn
    FROM TransactionInfo
)

SELECT
    AccountNumber,
    SUM(Credit) AS TotalCredit,
    SUM(Debit) AS TotalDebit,
    MAX(CASE WHEN rn = 1 THEN CurrentBalance END) AS CurrentBalance
FROM cte
GROUP BY
    AccountNumber;

Upvotes: 1

manish Lad
manish Lad

Reputation: 1

if you Want to ordering the output record in aggregate function , just write the ORDER BY [columnindex from your query] e.g. in your query , if you want to ordering your output by AccountName then you can use ORDER BY 1 [AccountName], ORDER BY 2 [SUM(CreditAmount)], ORDER BY 3 SUM(DebitAmount) etc...

Upvotes: 0

Related Questions