mysql add tag
Matt S (imported from SE)
I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1).  Is there a way I can do this in one query?

Eg:

Table `users` has a column called active

Table `clients` does not have a column called active

I want to get a count of users where active=1 and just get a count of clients.

Before you say "just hard code it" this is a query that's going inside a python script that could be run on numerous different databases and I have no way of knowing what tables my script will be selecting and if they have a column called `active`, and I would prefer to have just one query to do it all instead of two separate ones and relying on mysql to throw an error so I know to use the other one.
Top Answer
ypercubeᵀᴹ (imported from SE)
My first thought would be to use the `INFORMATION_SCHEMA` first, so you get to know (in one query for all tables in the MySQL instance) which tables have an `active` column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way, though, that works no matter if the table has such a column or not:

    SELECT 
      ( SELECT COUNT(*)
        FROM TableName AS t
        WHERE active = 1
      ) AS cnt
    FROM
      ( SELECT 1 AS active
      ) AS dummy ;

Tested at **[SQL-Fiddle][1]** (also available at **[db<>fiddle][2]**).

### How it works

If the table has a column named `active`, the query is "translated" as if it had:

        WHERE t.active = 1

If the table doesn't have a column named `active`, the query is "translated" as if it had:

        WHERE dummy.active = 1         -- which is true 

[1]: http://sqlfiddle.com/#!9/fe5e3/1
[2]: https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=c3a4e6d0b70f8c3f09bedaed5a828ba0&hide=10

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.