Getting total results per status

Question

I would like to get the total entries per status for each category group.

I have 3 statuses.

the best thing I can come up with is:

{exp:channel:entries channel="any" status="Open"}  {if count == total_results}{count}{/if}{/exp:channel:entries}
{exp:channel:entries channel="any" status="closed"}  {if count == total_results}{count}{/if}{/exp:channel:entries}
{exp:channel:entries channel="any" status="test"}  {if count == total_results}{count}{/if}{/exp:channel:entries}

I think there has to be a smarter way.

Any help would be appreciated. Thank you

Answer 1

For best performance and simplicity...

{exp:query sql="SELECT status as status_label, count(*) as total_results FROM exp_channel_titles GROUP BY status_label"}
    {total_results} results for {status_label}.
{/exp:query}

You can just output a specific status using a conditional:

{if status_label == "test"}
    {total_results}
{/if}

Or you can refine the search if you have many other statuses you don't want included:

SELECT status as status_label, count(*) as total_results FROM exp_channel_titles WHERE status IN ("open", "closed", "test") GROUP BY status_label

Note I've used "status_label" instead of leaving it as just "status" so that it can be used within a channel entries tag pair without conflict of it's "status" tag value.

Based on this answer, albeit exp:stats doesn't work as I have it turned off and doesn't allow you to refine to status.