Subquery SQL during dataprep

[Bunnydata]

Hello,

Is it possible to write this subquery using SQL in data prep?

SELECT col1, col2, col3,
(SELECT AVG(col3)
FROM subquery AS subquery 2
WHERE subquery2.col2 <= subquery.col2) AS “average col3”)
FROM (
//subquery codes here//) AS subquery

I have no problem with writing this code without the subquery part starting with “SELECT AVG(col3)…”. But when I write the full code above there is an error

sourceErrorCode: 0
sourceErrorMessage:ERROR: relation “subquery” does not exist
sourceErrorState: 42P01
sourceException:org.postgresql.util.PSQLException
sourceType:REDSHIFT

[Sanjeeb2022]

Hi @Bunnydata - What is your source database? I personally prefer CTE ( Common Table Expression) over subquery. Can you give the exact requirement so that we can help you. Subquery should work but from performance stand point try to explore CTE.

Regards - Sanjeeb

[Bunnydata]

Hi @Sanjeeb2022 , my data is stored in RedShift.

I did post the exact requirement here trying to use calculated field but unable, so I try to use SQL. The solution provided in this question does not work.

Calculate average with condition

Hi @Bunnydata to that you can try: Select “Add Calculation” from the dropdown menu. Enter a name for the calculated field (e.g. “Avg Sales First 3 Months”). Use the following formula: IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG({Sales Amount}) OVER (PARTITION BY {Product} ORDER BY {Month of Sales} ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), “”) This will create a new calculated field that shows the average sales of the first 3 months for each product. If there are no …

[Sanjeeb2022]

hi @Bunnydata - can you please share your whole query to check this? also can you use CTE instead of sub query?

Regards - Sanjeeb

[Bunnydata]

thank you @Sanjeeb2022 . I was able to run this using CTE!!!

[Sanjeeb2022]

Thank you @Bunnydata . Can you please marked the suggestion as solution so that it can help to other community member.

Regards - Sanjeeb