Help with CTE syntax in SQL

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:

<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> ]   

^{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_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
)
SELECT * FROM CTE_Grand_Harvest_Total;

Then you are compliant with the SELECT and CTE definitions.