Thread

Hi

I have written this query and it is running juts fine in ee sql manager.
It breaks when I embed it in ee template like so:

{exp:query sql="
    SELECT 
        SUM(field_id_2+field_id_3+field_id_4+field_id_5+field_id_6+field_id_7)/(6*(
            SELECT 
                COUNT(entry_id) 
            FROM 
                exp_channel_titles 
            AS
                average
            WHERE 
                channel_id="1" 
            AND 
                status="final"))
            FROM 
                exp_channel_data 
            WHERE 
                entry_id IN 
                (SELECT entry_id         
                FROM exp_channel_titles 
                WHERE status="final");
    "}
    {average}
{/exp:query}

The query creates an average of field type values across entries. No error message.
Thanks

[Mod Edit: Moved to the CodeShare Corner]

Try swapping double quotes for single quotes around 1, final etc, failing that put query all on one line.

Hi BohBoh

that seemed to have help a bit since the page now at least renders the html.

But still no value in the {average} variable, any ideas?

Cheers

Try putting “AS average” after your subquery that calculates the average. That way, it’ll assign the alias to the results of that subquery. Ex:

...AND status="final")) AS average FROM exp_channel_data…

Hi Kevin
Thanks, that solved it!
Cheers