GROUP BY + SORT BY

[Reid]

I am using this FQL to generate data for a chart. The chart works fine when I have just the GROUP BY clause, but as soon as I add the SORT BY clause, it gives me an error "All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause."

Does FQL not let you use both clauses at the same time?

Let ( [ ~sql = "

    SELECT w.~month, SUM(w.~smh)

    FROM ~workorders w

    WHERE w.~resort = 1 AND w.~date = 1

       GROUP BY w.~month

       /* ORDER BY w.~created */ <------ Stops working if I remove the comments.

    

    ";

 

    $sqlQuery = Substitute ( ~sql ;

        [ "~workorders" ; SQLTableName ( WORKORDERS::WorkOrder ) ];

        [ "~resort" ; SQLFieldName ( WORKORDERS::cRework ) ];

        [ "~wo" ; SQLFieldName (WORKORDERS::WorkOrder ) ];

        [ "~smh" ; SQLFieldName ( WORKORDERS::sManHours ) ];

        [ "~created" ; SQLFieldName ( WORKORDERS::timestamp_create ) ];

        [ "~year" ; SQLFieldName ( WORKORDERS::YearCreated ) ];

        [ "~monthnumber" ; SQLFieldName ( WORKORDERS::MonthCreatedNumber ) ];

        [ "~date" ; SQLFieldName ( WORKORDERS::DateCreated11mo ) ];

        [ "~month" ; SQLFieldName ( WORKORDERS::MonthCreated ) ]

    

    );

 

    $$CALLED = List ( /*$$CALLED*/ "" ; Get (CurrentTimeUTCMilliseconds) );

    $sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" )

];

    //SQLDebugResult(ExecuteSQL ( $sqlQuery ; "" ; "" ))

    //$sqlQuery &\¶&

    If ( $sqlResult = "?" ;

        False;

        Let ( $$CHART.DATA = $sqlResult ; False )

    )

)

[otmar]

I think you need to include your sort-field in the GROUP BY clause, but then, without having the field in the SELECT, you'll have multiple entries per month with no specification. Maybe you just want to sort by month?

 

SELECT w.~month, SUM(w.~smh)

FROM ~workorders w

WHERE w.~resort = 1 AND w.~date = 1

GROUP BY w.~month, w.~created

ORDER BY w.~created

[Reid]

Thanks Otmar for the response. It looks like the ORDER BY clause fields have to be in the SELECT statement, which then you have to include them in the GROUP BY clause. If you group by the first one you need, the second one doesn't really matter that much. 

Here's my final query. 

Let ( [ ~sql = "

 

SELECT w.~monthnumber, w.~month, SUM(w.~smh*30)

 

FROM ~workorders w    

 

WHERE w.~resort = 1 AND w.~date = 1 

 

       GROUP BY w.~monthnumber, w.~month 

 

       ORDER BY w.~monthnumber