It seems like Scalar Subquery is not available in AlaSQL.

[hotsun1508]

Hi, I thought sub query was working in AlaSQL(https://github.com/AlaSQL/alasql/wiki/Subqueries) however, this scalar subquery seems like it's not working.

• Query Explanation: This query calculates the average profit per customer by education level and annual income, and compares it with the previous period. It assumes that "previous period" means the previous stock period.

<SQL-99>
SELECT 
    [educationlevel],
    [annualincome],
    AVG([totalprofit]) AS [average_profit_current_period],
    (SELECT AVG([totalprofit]) 
     FROM [temptable] AS previous
     WHERE previous.[educationlevel] = current.[educationlevel]
       AND previous.[annualincome] = current.[annualincome]
       AND previous.[stockperiod] = current.[stockperiod] - 1) AS [average_profit_previous_period]
FROM 
    [temptable] AS current
WHERE 
    [educationlevel] IS NOT NULL 
    AND [annualincome] IS NOT NULL 
    AND [totalprofit] IS NOT NULL
GROUP BY 
    [educationlevel], [annualincome]
ORDER BY 
    [educationlevel], [annualincome];

This is the Error Message I'm facing.

runQuery.js:25 Failed to execute query: 
TypeError: Cannot read properties of undefined (reading 'educationlevel')
    at pr.eval [as wherefn] (eval at T.Select.compileWhere (alasql@4:78:9531), <anonymous>:3:76)
    at Wt (alasql@4:60:6110)
    at Wt (alasql@4:60:7141)
    at Er (alasql@4:59:1015)
    at br (alasql@4:59:926)
    at l.options.autocommit.l.databases.<computed>.engineid.l.databases.<computed>.tables.<computed>.view.s.datafn (alasql@4:78:2021)
    at alasql@4:59:429
    at Array.forEach (<anonymous>)
    at Ts (alasql@4:59:389)
    at Array.u (alasql@4:77:648)

Just to make sure that the query itself has no issue, I've tried to run it in PostgreSQL, indeed I got a decent result.

SELECT 
    educationlevel,
    annualincome,
    AVG(totalprofit) AS average_profit_current_period,
    (SELECT AVG(previous.totalprofit) 
     FROM adventureworks."adventureworks-total" AS previous
     WHERE previous.educationlevel = current.educationlevel
    ) AS average_profit_previous_period
FROM 
    adventureworks."adventureworks-total" AS current
WHERE 
    educationlevel IS NOT NULL 
    AND annualincome IS NOT NULL 
    AND totalprofit IS NOT NULL
GROUP BY 
    educationlevel, annualincome
ORDER BY 
    educationlevel, annualincome;

Following is the screenshot of 5 head rows from the result table.

Please help me resolve this error.
Thank you so much :)