phpmyadmin Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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]
                  );
    Login or Signup to reply.
  2. 0

    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]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search