Select from a result of another select

I am trying to select results from the result of another select but I am getting an error. Below is the sql query.

select reference,date, 
       bdx_name,
       bdx_id,
       description,
       amount,
       cumulativetotal 
  from (select reference,
               date,
               bdx_name,
               bdx_id,
               description,
               amount,
               SUM(amount) OVER (PARTITION BY bdx_id order by date) AS cumulativetotal
          from transaction_running_balance
         where bdx_id = '33')
 where date(date) = '2021-03-01';

Answer

In case you are using MariaDB 10.2, which is the first version to accept window functions, have a look at the SQL Fiddle I just prepared with random data. Take into account that for DDL purposes, I changed the date column name to date_.

You either create a CTE to control the data, which to me is easier to read than a nested SELECT statement:

with temp_table as (
  select 
    reference, 
    date_, 
    bdx_name, 
    bdx_id, 
    description, 
    amount, 
    SUM(amount) OVER (PARTITION BY bdx_id ORDER BY date_) AS cumulativetotal
  from transaction_running_balance
  where bdx_id = 33
 )
 select * from temp_table where date(date_) = '2021-03-01';

Or give a name to the nested query you just made at the end of it after closing parentheses:

select reference,
       date_, 
       bdx_name,
       bdx_id,
       description,
       amount,
       cumulativetotal 
  from (select reference,
               date_,
               bdx_name,
               bdx_id,
               description,
               amount,
               SUM(amount) OVER (PARTITION BY bdx_id order by date_) AS cumulativetotal
          from transaction_running_balance
         where bdx_id = 33) AS nested_query
 where date(date_) = '2021-03-01';

Same result for both cases 🙂