Skip to content
Advertisement

Count number of occurrences in SQL

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();

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement