Clever Geek Handbook

Summing many tables in mysql

I have a task to compose a pivot table from several thousand other tables containing thousands of records. The pivot table should contain:

  • a non-redundant list of "entries";
  • the number of tables containing this "record"
  • the names of the tables containing this "record"

Example:

Table_1
nameA
nameB
nameC

Table_2
nameB
nameC
nameD

Table_3
nameC
nameD
nameE

The result should be something like this

Record_name  Number  Table_names
nameA        1       Table_1
nameB        2       Table_1, Table_2
nameC        3       Table_1, Table_2, Table_3
nameD        2       Table_2, Table_3
nameE        1       Table_3

It may not be difficult, but since I am completely new to mysql

, I hope to get advice and help from you!

+3


source to share


1 answer 


SELECT  col1 as `Record Number`, 
        COUNT(*) as `Number`, 
        GROUP_CONCAT(_name) as `table name`
FROM
(
    SELECT col1, 'table_1' as _name FROM table_1
    UNION ALL
    SELECT col1, 'table_2' as _name FROM table_2
    UNION ALL
    SELECT col1, 'table_3' as _name FROM table_3
) a
GROUP BY col1


+3


source






More articles:


All Articles