I need to get a column count for a mysql db table

[witgrefe]

I am trying to get a column count of the number of columns in a table called Soloists; the database is myCDDB.

I understand that the information is to be found in INFORMATION_SCHEMA.COLUMNS and I think that the following select statement should be used:

Select Count(*) From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME='Soloists' and TABLE_SCHEMA = 'myCDDB'"

but how do I get a count value from this statement.

 

[dabadepdu]

I don't really get the question. Why "should be"? Why didn't You try? The query is correct.
- You might run into problems with upper and lower case writing, depending on how the table create statement was written.
work around this with case insensitiv filter conditions
- You might want to access the result value in a high level language and don't know how to address the result.
Add a column alias to the statement like this i.e., so you exactly do know its name :

Select Count(*) as column_count_ From ..

or access result by fieldindex, it's only 1 column in the result.

BTW: This is a German speaking Forum. So let's switch to German.

 

[ukulele]

Your statement will count the pre defined columns for the table "Soloists". If you want to count set values you will need to query the table itself and also you would have to check each column on its own for NULL values. I would asume you want to view the result per row, but you could also build some kind of average over all rows - for whatever that would be useful

My guess is that you are looking for something like this:

SELECT id,
(CASE WHEN column1 IS NULL THEN 0 ELSE 1 END) +
(CASE WHEN column2 IS NULL THEN 0 ELSE 1 END) +
(CASE WHEN column3 IS NULL THEN 0 ELSE 1 END)
FROM Soloists