Sum with Multi Category and Group By Acc No ?

Question

Post reply

Sum with Multi Category and Group By Acc No ?

chinye2020

SSC Veteran

Points: 288
More actions
September 18, 2012 at 1:11 pm

#281775

SELECT SUM(AMOUNT) as SumAmount,TransType_ID,Transaction_AccNo
FROM Tbl_Transaction
Group By TransType_ID,Transaction_AccNo
below is the sql query result
SumAmount TransType_ID Transaction_AccNo
65000.00001
-35157.00031
-1872.23521
Question = i want to show only the Sum Amount With different TransType ID column and group by Acc No? ,example
SumAmount_TransType_ID_0 SumAmount_TransType_ID_3 SumAmount_TransType_ID_2 Transaction_AccNo
65000.000 -35157.000 -1872.235 1
how is the sql query??

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

mickyT SSChampion Points: 10360 More actions · Answer 1

Hi

You could try the following. Of course it will only work for the three TransType_IDs

SELECT SUM(CASE WHEN TransType_ID = 0 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_0

, SUM(CASE WHEN TransType_ID = 2 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_2

, SUM(CASE WHEN TransType_ID = 3 THEN AMOUNT ELSE null END) SumAmount_TransType_ID_3

, Transaction_AccNo

FROM Tbl_Transaction

GROUP BY Transaction_AccNo

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

For a dynamic solution, you should read this article:

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

[/url]

And maybe the first part as well.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

chinye2020 SSC Veteran Points: 288 More actions · Answer 3

SELECT

Tbl_Account.AccNo,

SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,

SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,

SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,

SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,

SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,

SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,

SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,

SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,

(SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance

FROM Tbl_Account

LEFT JOIN Tbl_Transaction ON Tbl_Account.AccNo = Tbl_Transaction.Transaction_AccNo AND (SELECT convert(Date,Tbl_Transaction.Transaction_Date, 105)) BETWEEN (SELECT convert(Date,'16-9-2012', 105)) AND (SELECT convert(Date,'30-9-2012', 105))

LEFT JOIN Tbl_Topup ON Tbl_Transaction.Topup_ID=Tbl_Topup.Topup_ID

Group By Tbl_Account.AccNo

ORDER BY Tbl_Account.AccNo

chinye2020 SSC Veteran Points: 288 More actions · Answer 4

chinye2020

SSC Veteran

Points: 288

September 18, 2012 at 10:54 pm

#1539382

i totally no idea with this,pls help~!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

chinye2020 (9/18/2012)
...(SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance
...

Write a separate query for this, then integrate it into the main query. We'll help with that part.

The problem is, "the first Amount" isn't easily understood with the information we have so far. Do you mean the first transaction against an account within the specified date range?

In the meantime, here's your original query with two key improvements:

Table aliases used throughout

Date filter is now SARGable - if there's an index on transaction date, it can now be used.

SELECT

a.AccNo,

SUM( case when t.TransType_ID = 0 then a.Amount else 0 end ) as Total_Adjustment,

SUM( case when t.TransType_ID = 1 then a.Amount else 0 end ) as Total_Topup,

SUM( case when t.TransType_ID = 2 then a.Amount else 0 end ) as Total_Comm,

SUM( case when t.TransType_ID = 3 then a.Amount else 0 end ) as Total_Transfer,

SUM( case when t.TransType_ID = 4 then a.Amount else 0 end ) as Total_RecvTransfer,

SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then a.Amount else 0 end ) as Total_Maxis,

SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then a.Amount else 0 end ) as Total_Digi,

SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then a.Amount else 0 end ) as Total_Celcom--,

--(SELECT Top 1 Amount as Open_Balance)<<---how to do this at here?i want to get the first Amount as Opening Balance

FROM Tbl_Account a

LEFT JOIN Tbl_Transaction t

ON a.AccNo = t.Transaction_AccNo

-- this date arithmetic should provide the same filter as the original

-- but is SARGable (use Google)

AND t.Transaction_Date > convert(Date,'16-9-2012', 105)

AND t.Transaction_Date < convert(Date,'01-10-2012', 105)

LEFT JOIN Tbl_Topup p ON t.Topup_ID = p.Topup_ID

Group By a.AccNo

ORDER BY a.AccNo

Edit: speling

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chinye2020 SSC Veteran Points: 288 More actions · Answer 6

chinye2020

SSC Veteran

Points: 288

September 19, 2012 at 6:23 am

#1539509

i mean select the top 1 Amount in the sql query

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

chinye2020 (9/19/2012)
i mean select the top 1 Amount in the sql query

Ordered by what? Amount? Transaction date?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chinye2020 SSC Veteran Points: 288 More actions · Answer 8

ChrisM@Work (9/19/2012)
chinye2020 (9/19/2012)
i mean select the top 1 Amount in the sql query
Ordered by what? Amount? Transaction date?

all Sum will according the TrasnType_ID and Acc No, so Result will show the all Acc No and their TransType Total Amount,

and i just want in the Tbl_Transaction where is Top 1 Amount and show the Top 1 Amount as Opening_Balance, that's all

chinye2020 SSC Veteran Points: 288 More actions · Answer 9

with CTE as

(

SELECT

Transaction_AccNo,

Amount,

TransType_ID,

Topup_ID,

ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo

ORDER BY convert(Date,Transaction_Date, 105)) as RowNum

from Tbl_Transaction

where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-1' AND '2012-09-30'

)

select

Tbl_Account.AccNo,

SUM( case when TransType_ID = 0 then Amount else 0 end ) as Total_Adjustment,

SUM( case when TransType_ID = 1 then Amount else 0 end ) as Total_Topup,

SUM( case when TransType_ID = 2 then Amount else 0 end ) as Total_Comm,

SUM( case when TransType_ID = 3 then Amount else 0 end ) as Total_Transfer,

SUM( case when TransType_ID = 4 then Amount else 0 end ) as Total_RecvTransfer,

SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='M' then Amount else 0 end ) as Total_Maxis,

SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='D' then Amount else 0 end ) as Total_Digi,

SUM( case when TransType_ID = 2 AND Tbl_Topup.Topup_Company='C' then Amount else 0 end ) as Total_Celcom,

SUM( case when RowNum = 1 then Amount else 0 end) as Open_Balance

FROM Tbl_Account

LEFT JOIN CTE ON Tbl_Account.AccNo = CTE.Transaction_AccNo

LEFT JOIN Tbl_Topup ON CTE.Topup_ID = Tbl_Topup.Topup_ID

Group By Tbl_Account.AccNo

ORDER BY Tbl_Account.AccNo

DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

SELECT

a.AccNo,

SUM( case when t.TransType_ID = 0 then t.Amount else 0 end ) as Total_Adjustment,

SUM( case when t.TransType_ID = 1 then t.Amount else 0 end ) as Total_Topup,

SUM( case when t.TransType_ID = 2 then t.Amount else 0 end ) as Total_Comm,

SUM( case when t.TransType_ID = 3 then t.Amount else 0 end ) as Total_Transfer,

SUM( case when t.TransType_ID = 4 then t.Amount else 0 end ) as Total_RecvTransfer,

SUM( case when t.TransType_ID = 2 AND p.Topup_Company='M' then t.Amount else 0 end ) as Total_Maxis,

SUM( case when t.TransType_ID = 2 AND p.Topup_Company='D' then t.Amount else 0 end ) as Total_Digi,

SUM( case when t.TransType_ID = 2 AND p.Topup_Company='C' then t.Amount else 0 end ) as Total_Celcom,

Option1.Open_Balance,

Option2.Open_Balance,

Option3.Open_Balance

FROM Tbl_Account a

LEFT JOIN Tbl_Transaction t

ON a.AccNo = t.Transaction_AccNo

-- this date arithmetic should provide the same filter as the original

-- but is SARGable (use Google)

AND t.Transaction_Date > convert(Date,'16-9-2012', 105)

AND t.Transaction_Date < convert(Date,'01-10-2012', 105)

LEFT JOIN Tbl_Topup p ON t.Topup_ID = p.Topup_ID

OUTER APPLY (-- first transaction of the date range, if there is one

SELECT TOP 1 Open_Balance = ti.Amount

FROM Tbl_Transaction ti

WHERE ti.Transaction_AccNo = a.AccNo -- outer ref

AND ti.Transaction_Date > convert(Date,'16-9-2012', 105)

AND ti.Transaction_Date < convert(Date,'01-10-2012', 105)

ORDER BY ti.Transaction_Date

) Option1

OUTER APPLY (-- largest value of a transaction, if there is one

SELECT TOP 1 Open_Balance = ti.Amount

FROM Tbl_Transaction ti

WHERE ti.Transaction_AccNo = a.AccNo -- outer ref

AND ti.Transaction_Date > convert(Date,'16-9-2012', 105)

AND ti.Transaction_Date < convert(Date,'01-10-2012', 105)

ORDER BY ti.Amount DESC

) Option2

CROSS APPLY ( -- SUM of all the amounts prior to date range

SELECT Open_Balance = SUM(ti.Amount)

FROM Tbl_Transaction ti

WHERE ti.Transaction_AccNo = a.AccNo -- outer ref

AND ti.Transaction_Date <= convert(Date,'16-9-2012', 105)

) Option3

Group By a.AccNo

ORDER BY a.AccNo

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

chinye2020 (9/19/2012)
...
DONE !, this is select Top 1 Amount,But how to select the LAST Amount as Closing_Balance?

Performance will be very poor. What datatype is Tbl_Transaction.Transaction_Date?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chinye2020 SSC Veteran Points: 288 More actions · Answer 12

with CTE as

(

SELECT

Transaction_AccNo,

Amount,Before_Amount,After_Amount,

TransType_ID,

Topup_ID,

ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo

ORDER BY Transaction_Date) as RowNum,

ROW_NUMBER() OVER(PARTITION BY Transaction_AccNo

ORDER BY Transaction_Date DESC) as LastRowNum

from Tbl_Transaction

where convert(Date,Transaction_Date, 105) BETWEEN '2012-09-16' AND '2012-09-30'