CODEWITHSUNDEEP
mysql
Denver Developers
Denver Developers

Reputation: 13

Query error Unknown column 'Count' in 'field list'

I am trying to sum the picks column from all the tables that have house in the table name.

SELECT SUM(Picks) AS A 
  FROM (SELECT TABLE_NAME 
          FROM information_schema.TABLES 
         WHERE TABLE_NAME LIKE '%house' AND 
               table_schema='myschema'
       ) AS T

This SQL statement results in the message

Unknown column 'Picks' in 'field list'

If I query just:

SELECT TABLE_NAME 
  FROM information_schema.TABLES 
 WHERE TABLE_NAME LIKE '%house' AND
       table_schema='myschema'

It returns the expected list of tables. I have verified each of the returned tables have a count column and there are all the same data type VARCHAR(45).

What am i missing?

Upvotes: 1

Views: 5925

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

This is your query:

SELECT SUM(Picks) AS A 
FROM (SELECT TABLE_NAME 
      FROM information_schema.TABLES 
      WHERE TABLE_NAME LIKE '%house' AND 
            table_schema='myschema'
    ) AS T

What you are missing is that the subquery does not have a column called Picks. I mean, the following would work:

SELECT SUM(Picks) AS A 
FROM (SELECT TABLE_NAME, 1 as Picks
      FROM information_schema.TABLES 
      WHERE TABLE_NAME LIKE '%house' AND 
            table_schema='myschema'
    ) AS T

Or this:

SELECT COUNT(*) as Picks
FROM (SELECT TABLE_NAME
      FROM information_schema.TABLES 
      WHERE TABLE_NAME LIKE '%house' AND 
            table_schema='myschema'
    ) AS T

But in your query, Picks isn't defined. Hence the error.

And, there is no such column in information_schema.tables either. That leads me to think that you are quite confused. Perhaps the column exists in the "house" tables. However, you cannot reference the tables like that. You may want to investigate prepared statements -- prepare/execute (see here), if you really want to dynamically put a table name into a query.

Upvotes: 1

Related Questions