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.
TopAnswers
Databases
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