I have a table with the following structure:
(table_name, column_name)
and for each row in this table I need to query the column_name in the table_name and do a COUNT(column_name) GROUP BY column_name
of the values in there.
Currently I do
SELECT * FROM this table /*and then*/ foreach row: do another query with: SELECT column_name, COUNT(column_name) GROUP BY column_name
Is there any way I can do this in a single query?
Something like
SELECT column_name, COUNT(column_name) GROUP BY column_name FOREACH(SELECT table_name, column_name FROM my_initial_table)
I know the last query is not valid, it’s just an example for what I am looking to to achieve if it’s possible.
LE:
The table that tells me where to look has 2 varchar columns
Ex:
|++++++++++++++++++++++++++++++++ | table_name | column_name | |+++++++++++++++++++++++++++++++| | logsa | foo | |===============================| | logsa | bar | |===============================| | testx | baz | |===============================|
This tells me that now i have too look in columns foo and bar of table logsa and column baz of table testx
Every column in every table has VARCHAR as datattype and i just need to count those that are the same. that’s way i was doing
SELECT column_name, COUNT(column_name) GROUP BY column_name
Advertisement
Answer
If you are working in MySql, you can’t directly use parametrized column names. There is an indirect way of doing this using stored procedures and prepared statements.
some sloppy first-draft code… notice the difference between backticks ` and quotes ‘
CREATE PROCEDURE CountTables() BEGIN DECLARE done TINYINT DEFAULT 0; DECLARE table_name varchar(30), colunn_name varchar(30); DECLARE cur1 CURSOR FOR SELECT table_name, column_name FROM ColumnTable; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; CREATE TEMPORARY TABLE t1( table_name varchar(30), column_name varchar(30), count int); OPEN cur1; START TRANSACTION; read_loop: LOOP FETCH FROM cur1 INTO table_name, column_name; IF done THEN LEAVE read_loop; END IF; SET insert_sql = CONCAT( "INSERT INTO `t1`(`table_name`, `column_name`, `count`) SELECT ", "'", table_name, "', '", column_name, "', count(`", column_name, "`)", " FROM `", table_name, "`" ); PREPARE insert_stmt FROM insert_sql; EXECUTE insert_stmt; DEALLOCATE PREPARE insert_stmt; END LOOP; COMMIT; SELECT * FROM t1 GROUP BY column_name; DROP TEMPORARY TABLE t1; END;
Oh ya, don’t forget to call the procedure:
CALL CountTables();