BigQuery: Group by table name

Question

My dataset (ds1) looks like the following:

2014_01_01_tableA
2014_01_01_tableB

2014_01_02_tableA
2014_01_02_tableB

The query:

SELECT date, COUNT(1) AS counter 
FROM [ds1.2014_01_01_tableA], [ds1.2014_01_01_tableB], [ds1.2014_01_02_tableA], [ds1.2014_01_02_tableB] 
GROUP BY date;

Returns:

date, counter
2014-01-01, 100
2014-01-02, 200

Is it somehow possible to group by the table name as well? Something like this maybe? ...

SELECT date, COUNT(1) AS counter, TABLE_NAME() AS table_name
FROM [ds1.2014_01_01_tableA], [ds1.2014_01_01_tableB], [ds1.2014_01_02_tableA], [ds1.2014_01_02_tableB] 
GROUP BY table_name, date;

Expected result:

date, counter, table_name
2014-01-01, 50, 2014_01_01_tableA
2014-01-01, 50, 2014_01_01_tableB
2014-01-02, 100, 2014_01_02_tableA
2014-01-02, 100, 2014_01_02_tableB

TABLE_NAME() AS table_name does not exist, unfortunately.

Answer 1

Unfortunately, there isn't a way to do this cleanly. I've added an internal feature request.

The ugly way to do it is:

SELECT date, COUNT(1) AS counter, table_name
FROM 
 (SELECT *, "2014_01_01_tableA" as table_name FROM [ds1.2014_01_01_tableA]), 
 (SELECT *, "2014_01_01_tableB" as table_name FROM [ds1.2014_01_01_tableB]), 
 (SELECT *, "2014_01_02_tableA" as table_name FROM [ds1.2014_01_02_tableA]), 
 (SELECT *, "2014_01_02_tableB" as table_name FROM [ds1.2014_01_02_tableB])
GROUP BY table_name, date;