MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

countMySQLselect

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.

Best Answer

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

Related Solutions

Mysql – How to COUNT number of rows with LIMIT

It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. Check out FOUND ROWS

SQL – How to Efficiently Count Rows by Limiting Found Rows

First, it's almost never OK to use LIMIT without ORDER BY, because you will not get consistent results across different executions of the query. So, especially for the pagination queries, I'd use:

SELECT <list of columns>
FROM <tables>
WHERE <conditions>
ORDER BY <some columns>
LIMIT rows_per_page
OFFSET x ;

Now, to find the number of pages this query could return - and with the extra condition that you will not use more than 50 pages - I'd try a separate query;

SELECT CEIL( COUNT(*) / 50 ) AS pages
FROM
  ( SELECT NULL
    FROM <tables>
    WHERE <conditions>
    LIMIT 50 * rows_per_page
  ) AS x ;

(and that is an exception to the "never use LIMIT without ORDER BY" rule above :)

Best Answer

Related Solutions

Mysql – How to COUNT number of rows with LIMIT

SQL – How to Efficiently Count Rows by Limiting Found Rows

Related Question