SQLTeam.com | Weblogs | Forums

Group By Issue

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

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.

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

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