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
– Strawberry, Commented Mar 1, 2019 at 13:57
I have removed those conflicting dbms tags. Put one of them back, the one for the dbms actually used. — jarlh
– jarlh, Commented Mar 1, 2019 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
– Viktor Bardakov, Commented Mar 1, 2019 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
– Muhammad Hamza, Commented Mar 1, 2019 at 14:15

Bill Karwin · Accepted Answer · 2019-03-01 14:43:59Z

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.

Thomas G · Accepted Answer · 2019-03-01 14:03:44Z

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 1, 2019 at 14:03

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

4 Comments

bstent Over a year ago

although I didn't down vote , but I don't have the table names, those should be dynamically selected from mapping table.

Bill Karwin Over a year ago

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.

Thomas G Over a year ago

@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.

Bill Karwin Over a year ago

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.

Collectives™ on Stack Overflow

sql - group by using dynamically selected table names

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related