CODEWITHSUNDEEP
sqlexcelvba
Kry Chulsa
Kry Chulsa

Reputation: 7

How to fix "query does not include the specified expression 'credit usd' as part of an aggregate function"?

I'm try to select "max(TxnDate) as txDate, [account name dr], [credit usd]" and I got an error "query does not include the specified expression 'credit usd' as part of an aggregate function" in excel VBA programming but in work find when I tried in SQL sever or PHPMyadmin

I'm working on MSExcel 2013 with VBA programming

SELECT max(TxnDate) as txDate, [account name dr], [credit usd] from 
   (
       SELECT t.TxnDate, t.[account name dr], t.[credit usd] FROM 
       ( 
           select [account name dr], max([credit usd]) as max_c from [FT_Pure$] GROUP BY [account name dr]
       ) as a INNER JOIN [FT_Pure$] as t on (t.[account name dr] = a.[account name dr] and t.[credit usd] = max_c)
   ) as ta group by ta.[account name dr]

I expect the output should list of txDate, account_name_dr and Credit_Usd

Here is the error message:

Error messsage

Upvotes: 0

Views: 70

Answers (2)

iainc
iainc

Reputation: 868

The outer GROUP BY clause is missing [credit usd] which you have in the outer select along with [account name dr].

Just add it in:

SELECT max(TxnDate) as txDate, [account name dr], [credit usd] from 
   (
       SELECT t.TxnDate, t.[account name dr], t.[credit usd] FROM 
       ( 
           select [account name dr], max([credit usd]) as max_c from [FT_Pure$] GROUP BY [account name dr]
       ) as a INNER JOIN [FT_Pure$] as t on (t.[account name dr] = a.[account name dr] and t.[credit usd] = max_c)
   ) as ta group by ta.[account name dr], [credit usd]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you are trying to get the most recent row for each account, then aggregation is not what comes to mind. You can use filtering:

select t.*
from [FT_Pure$] t
where t.TxnDate = (select max(t2.TxnDate)
                   from [FT_Pure$] t2
                   where t2.[account name dr] = t.[account name dr]
                  );

Upvotes: 0

Related Questions