# Subquery SQL during dataprep

**URL:** <https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413>\
**Category:** Q&A\
**Tags:** SQL, data-preparation, quick-sight\
**Created:** [May 13, 2023, 10:48pm UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413 "2023-05-13T22:48:30Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bunnydata](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@Bunnydata](https://community.amazonquick.com/u/Bunnydata)\
**Post date:** [May 13, 2023, 10:48pm UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413/1 "2023-05-13T22:48:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Sanjeeb2022](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/sanjeeb2022/32/20947_2.png) [@Sanjeeb2022](https://community.amazonquick.com/u/Sanjeeb2022)\
**Post date:** [May 14, 2023, 7:43am UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413/2 "2023-05-14T07:43:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Bunnydata](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@Bunnydata](https://community.amazonquick.com/u/Bunnydata)\
**Post date:** [May 18, 2023, 5:42pm UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413/3 "2023-05-18T17:42:03Z")

</div>

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](https://community.amazonquick.com/t/calculate-average-with-condition/13336/2):
>
> 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 …

---

<div class="post-metadata">

**Author:** ![Sanjeeb2022](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/sanjeeb2022/32/20947_2.png) [@Sanjeeb2022](https://community.amazonquick.com/u/Sanjeeb2022)\
**Post date:** [May 18, 2023, 6:39pm UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413/4 "2023-05-18T18:39:13Z")

</div>

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

Regards - Sanjeeb

---

<div class="post-metadata">

**Author:** ![Bunnydata](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@Bunnydata](https://community.amazonquick.com/u/Bunnydata)\
**Post date:** [May 21, 2023, 1:21am UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413/5 "2023-05-21T01:21:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Sanjeeb2022](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/sanjeeb2022/32/20947_2.png) [@Sanjeeb2022](https://community.amazonquick.com/u/Sanjeeb2022)\
**Post date:** [May 21, 2023, 7:31am UTC](https://community.amazonquick.com/t/subquery-sql-during-dataprep/13413/6 "2023-05-21T07:31:32Z")

</div>

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

Regards - Sanjeeb