Group By Issue

1

Hello all.

When I attempt to run the query below, I am met with an error. Since AccountHistory.MemberNumber and AccountHistory.AccountNumber are in a subquery, why would I need to include them in the Group By section? What am I missing? Thank you.

Msg 8120, Level 16, State 1, Line 2
Column 'AccountHistory.MemberNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'AccountHistory.AccountNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Select Distinct
BIN = Case When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) In (543940,534025) Then 420477
** When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) Is Null AND (Select Max(dc.CreditCardAccount) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.BIN = 581551) = 0 Then 581551**
** When (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber) Is Null OR (Select Max(dc.CreditCardAccount) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.BIN = 420477) <> 0 Then 420477 **
** Else (Select Max(dc.BIN) From DebitCard dc Where dc.MemberNumber = ah.MemberNumber AND dc.CreditCardAccount = ah.AccountNumber)**
** End**,
Month = Month(ah.EntryDate),
Year = Year(ah.EntryDate),
Trans_Type = Case When ah.TransactionCodeKey = 236 Then 'Debit Card Signature Transaction'
When ah.TransactionCodeKey = 230 Then 'Debit Card Cash Advance'
When ah.TransactionCodeKey = 98 Then 'Debit Card Merchandise Return'
When ah.TokenString Like '%9%875%300%18%POS%' Then 'Debit Card POS'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount <0 Then 'ATM Withdrawal'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount >0 Then 'ATM Deposit'
When ah.TokenString Like '%1%9%742%100%18%' AND ah.TransactionCodeKey = 186 AND ah.PrincipalAmount<0 Then 'JNC Withdrawal'
When ah.TokenString Like '%1%9%742%100%18%' AND ah.TransactionCodeKey = 67 AND ah.PrincipalAmount>0 Then 'JNC Deposit'
Else 'None'
End,
Total_Number = Sum(Case When ah.PrincipalAmount <> 0 Then 1 Else 0 End),
Total_Dollar = Sum(Case When ah.PrincipalAmount <> 0 Then ah.PrincipalAmount Else 0 End)

From
AccountHistory ah

Where
ah.EntryDate Between '01/01/2020' AND '09/30/2020'
AND ah.AccountNumber Not Between 105 and 135
AND Case When ah.TransactionCodeKey = 236 Then 'Debit Card Signature Transaction'
When ah.TransactionCodeKey = 230 Then 'Debit Card Cash Advance'
When ah.TransactionCodeKey = 98 Then 'Debit Card Merchandise Return'
When ah.TokenString Like '%9%875%300%18%POS%' Then 'Debit Card POS'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount <0 Then 'ATM Withdrawal'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount >0 Then 'ATM Deposit'
When ah.TokenString Like '%1%9%742%100%18%' AND ah.TransactionCodeKey = 186 AND ah.PrincipalAmount<0 Then 'JNC Withdrawal'
When ah.TokenString Like '%1%9%742%100%18%' AND ah.TransactionCodeKey = 67 AND ah.PrincipalAmount>0 Then 'JNC Deposit'
Else 'None'
End <> 'None'
--AND ah.TransactionCodeKey In (67,186,230,98,236)

Group By
Month(ah.EntryDate),
Year(ah.EntryDate),
Case When ah.TransactionCodeKey = 236 Then 'Debit Card Signature Transaction'
When ah.TransactionCodeKey = 230 Then 'Debit Card Cash Advance'
When ah.TransactionCodeKey = 98 Then 'Debit Card Merchandise Return'
When ah.TokenString Like '%9%875%300%18%POS%' Then 'Debit Card POS'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount <0 Then 'ATM Withdrawal'
When ah.TokenString Like '%9%875%300%18%ATM%' AND ah.PrincipalAmount >0 Then 'ATM Deposit'
When ah.TokenString Like '%1%9%742%100%18%' AND ah.TransactionCodeKey = 186 AND ah.PrincipalAmount<0 Then 'JNC Withdrawal'
When ah.TokenString Like '%1%9%742%100%18%' AND ah.TransactionCodeKey = 67 AND ah.PrincipalAmount>0 Then 'JNC Deposit'
Else 'None'
End

2

Can you post the table structure with sample data.
You can join the DebitCard table with AccountHistory in the main query to simplify the subqueries.

3

DDL and sample data would help quite a bit. But based on what I see, you would need to group by the "bin" calculation as well. Don't need distinct either

4

Thanks for the replies. I did try the join, but it is slow and I couldn't get an accurate result.

I am trying to get the total by Month/Year, BIN, and Trans Type.

So the results would look something like this:
image