sql - group by using dynamically selected table names

Question

I have a table called app_to_tables which is mapping between apps and table names, table names are around 20, so I cannot hard code them in the query. Here for simplicity I put just 2 table names (table_1, table_2). Table structure is the following:

+--------+------------+
 | app_id | table_name |
 +--------+------------+
 |      1 | table_1    |
 |      1 | table_2    |
 |      2 | table_1    |
 |      2 | table_2    |
 +--------+------------+

from the other hand I have another tables, which named as table_1, table_2, table_3. The schema for these tables are the same, and the content is the following:

table_1
+------------+--------+--------+
|    date    | app_id | signal |
+------------+--------+--------+
| 2018-01-01 |      1 | sg     |
| 2018-01-01 |      1 | sg     |
| 2018-01-01 |      2 | sg     |
| 2018-01-01 |      2 | sg     |
| 2018-01-02 |      1 | sg     |
| 2018-01-02 |      1 | sg     |
+------------+--------+--------+

table_2
+------------+--------+--------+
|    date    | app_id | signal |
+------------+--------+--------+
| 2018-01-01 |      1 | sg     |
| 2018-01-01 |      2 | sg     |
| 2018-01-01 |      2 | sg     |
| 2018-01-01 |      2 | sg     |
| 2018-01-02 |      1 | sg     |
| 2018-01-02 |      2 | sg     |
+------------+--------+--------+

I am trying to have an sql query outcome which will be the following: The number of signals per each date per each table_name per each app_id, like the following:

+--------+------------+------------+-------+
| app_id | table_name |    date    | count |
+--------+------------+------------+-------+
|      1 | table_1    | 2018-01-01 |     2 |
|      1 | table_1    | 2018-01-02 |     2 |
|      1 | table_2    | 2018-01-01 |     1 |
|      1 | table_2    | 2018-01-02 |     1 |
|      2 | table_1    | 2018-01-01 |     2 |
|      2 | table_2    | 2018-01-01 |     3 |
|      2 | table_2    | 2018-01-02 |     1 |
+--------+------------+------------+-------+

My main trouble is how I can select a table names and then dynamically (without hardcoding the table names in the query) use that table names to count the number of signals.

This kind of problem can be symptomatic of poor design. Consider having 1 table instead of 2 — Strawberry, Mar 01 '19 at 13:57
I have removed those conflicting dbms tags. Put one of them back, the one for the dbms actually used. — jarlh, Mar 01 '19 at 14:02
What kind of DB are you using? In any case. Just create temp table for result, start cycle on first table and fill temp table with dynamic queries — Viktor Bardakov, Mar 01 '19 at 14:03
Can you also please explain the purpose of the signal field? It sort of seems to not have any purpose at all, given that all that field has is `sg`'s. Life could have been much easier to simply count the rows instead imo. Please send my regards to the person who made this. — Muhammad Hamza, Mar 01 '19 at 14:15
the structure is much complex, I just put the dummy value here for simplicity, In fact the sig field contains real value from user click. — bstent, Mar 01 '19 at 14:17
Still completely unclear to me... Why would you "select a table name" and then count over multiple tables... Selecting an app_id and then count across all its tables referred in your app_to_tables, would have been logical. But here its not. — Thomas G, Mar 01 '19 at 14:18
Those table_1, table_2 are really dummy names here, In reality they are business tables with proper name and and structure/values. I made it simple here just not to overwhelm people with redundant information. — bstent, Mar 01 '19 at 14:21
I'm not sure but this might be able to solve your problem. What you need is a `foreach` iterator, but unfortunately mysql doesn't have that. The guy who answered [this question](https://stackoverflow.com/questions/7990227/can-you-do-a-for-each-row-loop-using-mysql) has given a link for an alternative. Never really used it personally but hope it helps! — Muhammad Hamza, Mar 01 '19 at 14:22
I understand but as @Strawberry pointed out the design is pretty off which makes our options limited. Although I believe there must be another solution to your problem that someone else may have, you could meanwhile try this out or simply wait for someone else to provide a better answer, or maybe both ;) (I'd choose both :D) — Muhammad Hamza, Mar 01 '19 at 14:30

score 2 · Accepted Answer · answered Mar 01 '19 at 14:43

My main trouble is how I can select a table names and then dynamically (without hardcoding the table names in the query) use that table names to count the number of signals.

Unfortunately, this is not possible in SQL. SQL requires that the query names all tables explicitly before the query is prepared. You can't write any SQL query that dynamically joins to additional tables depending on what values it discovers as it scans through data during execution.

It makes no more sense to SQL than asking for code that calls some function, whose name is in fact the return value of the function. Like in PHP, you can call a function whose name is based on a variable. But you clearly could not call a function whose name is the result of the function you call.

$result = $result(); // this is a paradox

What you can do is generate a query based on the distinct tables you find named in your app_to_tables table.

SELECT DISTINCT table_name FROM app_to_tables;

This gives you a short list of table names (perhaps around 20 tables in your case). Then you must write code to build an SQL query as a string from this list.

Basically like the solution given by @ThomasG on this thread.

score 1 · Answer 2 · answered Mar 01 '19 at 14:03

1

UNION is your friend

SELECT app_id, table_name, date, count(*)
FROM
( 
SELECT 'table_1' AS table_name, date, app_id FROM table_1
UNION ALL
SELECT 'table_2' AS table_name, date, app_id FROM table_2
UNION ALL
SELECT 'table_3' AS table_name, date, app_id FROM table_3
) U
GROUP BY app_id, table_name, date

answered Mar 01 '19 at 14:03

Thomas G

9,886
7
28
41

although I didn't down vote , but I don't have the table names, those should be dynamically selected from mapping table. – bstent Mar 01 '19 at 14:06
I agree with this solution. I did not downvote, but I recognize that strictly speaking it doesn't address the OP's requirement that the table names must not be hardcoded in the query. I know that's impossible, but you didn't address it. – Bill Karwin Mar 01 '19 at 14:45
@BillKarwin At the moment I posted this, the "not hardcoded" was not specified in the question, and my answer was making sense, but was nevertheless instantly downvoted, by a hater probably. @ hater : get a life. – Thomas G Mar 01 '19 at 14:59
Okay perhaps it was someone who doesn't like answers that have only a code solution (even if it is correct) with too little supporting text to explain it. I wouldn't worry about downvotes on Stack Overflow. They're pretty random and senseless. I wish downvotes weren't anonymous, and required a reason, like close votes, but Stack Overflow moderators have totally refused to do that. – Bill Karwin Mar 01 '19 at 15:02

sql - group by using dynamically selected table names

2 Answers2