Count the number of occurrences in SQL

Question

I have a table with the following structure:

(table_name, column_name) 

and for each row in this table, I need to query column_name in table_name and make COUNT(column_name) GROUP BY column_name values ​​there.

I'm currently doing

 SELECT * FROM this table /*and then*/ foreach row: do another query with: SELECT column_name, COUNT(column_name) GROUP BY column_name 

Is there a way to do this in a single request? Sort of

 SELECT column_name, COUNT(column_name) GROUP BY column_name FOREACH(SELECT table_name, column_name FROM my_initial_table) 

I know that the last request is invalid, this is just an example of what I want to achieve, if possible.

LE:

The table that indicates where to look has 2 varchar columns

Example:

 |++++++++++++++++++++++++++++++++ | table_name | column_name | |+++++++++++++++++++++++++++++++| | logsa | foo | |===============================| | logsa | bar | |===============================| | testx | baz | |===============================| 

This tells me that now I also look in the foo and bar columns of the logsa table and the baz column of the testx table

Each column in each table has a VARCHAR as a data type, and I just need to count the same. so i did

 SELECT column_name, COUNT(column_name) GROUP BY column_name 

Answer 1

If you are working in MySql, you cannot directly use parameterized column names. There is an indirect way to do this using stored procedures and prepared statements.

some sloppy first stage code ... pay attention to 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 yes, don't forget to call the procedure:

 CALL CountTables(); 

Answer 2

The subquery should help you here. This is an example (untested) that should work with some refinement. I'm going to name your Schema table for the purposes of my example, and in the subquery I'm going to do it like a ColumnCount to make the code (hopefully) more readable.

 SELECT Schema.table_name, Schema.column_name, (SELECT COUNT(*) FROM Schema ColumnCount WHERE Schema.column_name = ColumnCount.column_name) AS ColumnUsageCount FROM Schema 

Answer 3

No need to make a subheading, just do it if only one table is involved.

 SELECT max(table_name) as table_name, column_name, COUNT(*) as occurrence FROM initial_table GROUP BY column_name ORDER BY column_name 

Max(table_name) replaces the nonexistent function Whatever(table_name) .
In MySQL, you can also use group_concat(table_name) as table_names . Give it a try!

ORDER BY is optional (and not needed in MySQL), but it can be convenient if you want it to be sorted by column name.

If you want to list unique occurrences combi table_name + column_name do:

 SELECT table_name, column_name, COUNT(*) as occurrence FROM initial_table GROUP BY table_name, column_name ORDER BY table_name, column_name 

Answer 4

in php

 $result=mysql_query("SELECT column_name FROM $table_name"); $row_count = mysql_num_rows($result); 

I am not very familiar with mysql, but I think it is along these lines