How to select specific rows if a column exists or all rows if a column doesn't

Question

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.

Answer 1

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 or not such a column:

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

Tested at SQL-Fiddle 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 

Answer 2

Count of Clients Per UserID with Grand Total using only the users table:

SELECT
    IFNULL(u.id,'Total') UserID,
    COUNT(u.clientid) ClientCount
FROM users u
WHERE u.active = 1
GROUP BY u.id WITH ROLLUP;

If the clients table has deleted records, then do this:

SELECT
    IFNULL(u.id,'Total') UserID,
    COUNT(c.id) ClientCount
FROM users u INNER JOIN clients c
ON u.clientid = c.id
WHERE u.active = 1
GROUP BY u.id WITH ROLLUP;

For users tables that do not have an active column:

SELECT
    IFNULL(u.id,'Total') UserID,
    COUNT(u.clientid) ClientCount
FROM users u
GROUP BY u.id WITH ROLLUP;

or

SELECT
    IFNULL(u.id,'Total') UserID,
    COUNT(c.id) ClientCount
FROM users u INNER JOIN clients c
ON u.clientid = c.id
GROUP BY u.id WITH ROLLUP;

You would need to run these queries against each database and UNION ALL the results.

If you want to harness the INFORMATION_SCHEMA database then here is a wild guess:

SELECT COUNT(1) INTO @hasactive
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'users'
AND column_name = 'active';
SELECT
    IFNULL(u.id,'Total') UserID,
    COUNT(u.clientid) ClientCount
FROM users u
WHERE IF(@hasactive=1,u.active=1,1)=1
GROUP BY u.id WITH ROLLUP;

Give it a Try !!!

Answer 3

I would like to supplement the answer of the @ypercubeᵀᴹ, but i can`t write comments because of the restrictions.

If you do not know what column name is used, and you need to get its maximum value, you can do that:

SELECT
    ( SELECT
          max(ISNULL(updateTime, null)) + max(ISNULL(updDT, null))
          FROM tableName as t
    ) AS maxUpdateDT
FROM (SELECT 0 AS updateTime, 0 as updDT) AS dummy;