SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sum with Multi Category and Group By Acc No ?


Sum with Multi Category and Group By Acc No ?

Author
Message
chinye2020
chinye2020
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 41
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.000 0 1
-35157.000 3 1
-1872.235 2 1

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??
mickyT
mickyT
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 3318
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
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42454 Visits: 19838
For a dynamic solution, you should read this article:
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

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
chinye2020
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 41
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
chinye2020
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 41
i totally no idea with this,pls help~!
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42039 Visits: 20008
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
Exploring Recursive CTEs by Example Dwain Camps
chinye2020
chinye2020
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 41
i mean select the top 1 Amount in the sql query
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42039 Visits: 20008
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
Exploring Recursive CTEs by Example Dwain Camps
chinye2020
chinye2020
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 41
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
chinye2020
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 41
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search