SQL query issue while trying to count the entry (row)

Question

Trying to produce a current entry count / total entries within a channel_entry tag by using a SQL query.

{exp:channel:entries
  channel="{channel}"
  limit="1"
  require_entry="yes"
  status="open"
  disable="{disable_default}"
  orderby="title"
  sort="asc"
  }

    <!-- entry number (row number) -->
    {exp:query sql="
        SET @row_number:=0;
        SELECT @row_number:=@row_number+1 AS row_number,title FROM exp_channel_titles
        WHERE channel_id = 10 AND status ="open"
        ORDER BY title;
        "}
        {row_number}
    {/exp:query}

    /

    <!-- total entries -->        
    {exp:stats channel="{channel"}{total_results}{/exp:stats}

{/exp:channel:entries}

The Query works perfect while running it directly on the DB in the Navicat query editor. However it doesn't run (outputs nada) from within the template.

Answer 1

Could you not use a simpler query? I notice that your query is actually 2 queries (note, the 2 semi-colons)...

Does the following work (done using MySql native) :

{exp:query sql="SELECT COUNT(*) AS row_number FROM exp_channel_titles WHERE channel_id = 10 AND status ='open';"}
    {row_number}
{/exp:query}

Also, you've got some double quote hell going on, use double quote for the EE attribute and single quote inside the query (as per example)...

Edit

Seems I'm also missing the 'FROM' clause in my example, I have corrected the above.