patternsqlMinor

Help with CTE syntax in SQL

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

withsqlctehelpsyntax

Problem

I am new to SQL, I tried to create a CTE, but I get a red squiggle under the AS and I am getting an error message in the process of executing the syntax:

Msg 102, Level 15, State 1, Line 117 Incorrect syntax near ')'.

This is the SQL syntax, any help would be great:

WITH CTE_Grand_Harvest_Total AS (
SELECT 
    zardi, COUNT(zardi) AS Count_ZARDI, SUM(Weight) AS Sum_Weight, Total_Harvest, Total_Harvest*COUNT(zardi)*SUM(Weight) AS Grand_Total
FROM 
    S1_PH_CROPS_clean_final_minus_money_matters
WHERE  
    Work.dbo.S1_PH_CROPS_clean_final_minus_money_matters.cropNamePH = 'Maize'
GROUP BY 
    Total_Harvest, zardi
)

Solution

A CTE (common table expression) is actually only part of a SELECT statement. It's something you define before you actually SELECT data. You could look at it as a kind of pre-selection of data before you are actually retrieving data from that subset of data (from the CTE).

You can see this in the Microsoft Learn article explaining the SELECT statement:

 ::=    
    [ WITH { [ XMLNAMESPACES ,] [  [,...n] ] } ]  --    
    [ ORDER BY  ] 
    [ ]   
    [ OPTION (  [ ,...n ] ) ]   
 ::=   
    {  | (  ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
         | (  ) [...n ] ]   
 ::=   
SELECT [ ALL | DISTINCT ]   --    
    [ INTO new_table ]   
    [ FROM {  } [ ,...n ] ]   
    [ WHERE  ]   
    [  ]

Reference:

SELECT (Transact-SQL) (Microsoft Learn)

WITH common_table_expression (Transact-SQL) (Microsoft Learn)

You have to select the data from your CTE, similar to this:

WITH CTE_Grand_Harvest_Total AS (
SELECT 
    zardi, COUNT(zardi) AS Count_ZARDI, SUM(Weight) AS Sum_Weight, Total_Harvest, Total_HarvestCOUNT(zardi)SUM(Weight) AS Grand_Total
FROM 
    S1_PH_CROPS_clean_final_minus_money_matters
WHERE  
    Work.dbo.S1_PH_CROPS_clean_final_minus_money_matters.cropNamePH = 'Maize'
GROUP BY 
    Total_Harvest, zardi
)
SELECT * FROM CTE_Grand_Harvest_Total;

Then you are compliant with the SELECT and CTE definitions.

Code Snippets

<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  -- <== The CTE bit
    <query_expression>   
    [ ORDER BY <order_by_expression> ] 
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   -- <======== ACTUAL START OF A STANDARD SELECT
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]

Context

StackExchange Database Administrators Q#334942, answer score: 6

Revisions (0)

No revisions yet.