CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user1773949
user1773949

Reputation: 119

Sub group aggregation with Case

I'm trying to return the list of groupid's, masteraccountnames and CashAmountDiff where the sum of the CashAmount of the trades with the same groupid is great than 100 for any date.

The table schemas are like so:

TradeT1 

TradeId | SubAccountId | MasterAccId | GroupId | TradeDate | TradeType

MasterAccount 

Id | MasterAccName

SubAccount 

Id | SubAccName | MasterAccountId

Each SubAccount is linked to a MasterAccount. It is a Many-to-one relationship.

TradeType determines the 'direction' of the trade, whether it's debit or credit. I am having trouble adding the debits and credits per groupid. My query is just summing everything. I'm not sure how to incorporate the Case When Credit (multiply CashAmount * -1), Case When Debit just use CashAmount

There will always be more than 1 tradeid linked to a single groupid.

I have the tables in SQL fiddle here along with my effort: http://www.sqlfiddle.com/#!3/45580/1/0 

SELECT 
t.groupId,
ma.MasterAccName,
sum(CASE WHEN t.tradetype = 'Credit' 
       THEN sum(-1*t.cashamount)
    ELSE sum(t.cashamount)) 
END as CashDiff 
FROM tradet1 t
JOIN masteraccount ma
ON t.masteraccid = ma.id 
WHERE t.groupid > -1
GROUP BY t.groupid, ma.MasterAccName
HAVING count(t.groupid) > 1 and sum(t.cashamount) > 100

Any help appreciated.

Upvotes: 1

Views: 122

Answers (2)

Ken Rachynski
Ken Rachynski

Reputation: 320

You can simplify this query quite a bit by converting your credits and debits in a sub-query and using those results in your aggregate query.

Such as

SELECT    [tt].[GroupId] ,
          [ma].[MasterAccName] ,
          CASE WHEN [tt].[TradeType] = 'Credit'
               THEN [tt].[CashAmount] * -1
               ELSE [tt].[CashAmount]
               END AS CashAmount
FROM      [dbo].[TradeT1] AS tt
          JOIN [dbo].[MasterAccount] AS ma ON [tt].[MasterAccId] = [ma].[Id]

With this, you have a nice list of CashAmounts as positive and negative to use in a surrounding aggregate query.

The whole query would look like this

SELECT  [tn].[GroupId] ,
        [tn].[MasterAccName] ,
        SUM([tn].[CashAmount]) AS CashDiff
FROM    ( SELECT    [tt].[GroupId] ,
                    [ma].[MasterAccName] ,
                    CASE WHEN [tt].[TradeType] = 'Credit'
                         THEN [tt].[CashAmount] * -1
                         ELSE [tt].[CashAmount]
                    END AS CashAmount
          FROM      [dbo].[TradeT1] AS tt
                    JOIN [dbo].[MasterAccount] AS ma ON [tt].[MasterAccId] = [ma].[Id]
        ) AS tn
GROUP BY [tn].[GroupId] ,
        [tn].[MasterAccName]
HAVING  ABS(SUM([tn].[CashAmount])) > 100 AND COUNT([tn].[GroupId]) > 1

Upvotes: 1

Laurence
Laurence

Reputation: 10976

This is just your query adapted for +/-. I might have the signs backkwards.

Select 
  t.groupId,
  ma.MasterAccName,
  Sum (Case When TradeType = 'Credit' Then -t.cashamount Else t.cashamount End) AS 'CashDiff'
From 
  tradet1 t
      Inner Join
  masteraccount ma
      On t.masteraccid = ma.id 
 Where
  t.groupid > -1
 Group By
  t.groupid,
  ma.MasterAccName
Having
  Count(t.groupid) > 1 And
  Sum (Case When TradeType = 'Credit' Then -t.cashamount Else t.cashamount End) > 100

http://www.sqlfiddle.com/#!3/45580/20

Upvotes: 1

Related Questions