Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Sum with Multi Category and Group By Acc No ? Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 1:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:05 AM
Points: 32, 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??
Post #1360975
Posted Tuesday, September 18, 2012 2:08 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:59 PM
Points: 951, Visits: 2,887
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

Post #1361013
Posted Tuesday, September 18, 2012 2:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 3,354, Visits: 7,251
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1361014
Posted Tuesday, September 18, 2012 2:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:05 AM
Points: 32, 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
Post #1361022
Posted Tuesday, September 18, 2012 10:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:05 AM
Points: 32, Visits: 41
i totally no idea with this,pls help~!
Post #1361133
Posted Wednesday, September 19, 2012 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 7,123, Visits: 13,496
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
Post #1361187
Posted Wednesday, September 19, 2012 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:05 AM
Points: 32, Visits: 41
i mean select the top 1 Amount in the sql query
Post #1361284
Posted Wednesday, September 19, 2012 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:37 AM
Points: 7,123, Visits: 13,496
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
Post #1361300
Posted Wednesday, September 19, 2012 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:05 AM
Points: 32, 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
Post #1361303
Posted Wednesday, September 19, 2012 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 7:05 AM
Points: 32, 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?
Post #1361308
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse