I am trying to create a table that counts unique ID’s among several other tables. I have gotten as far as getting the count and sorting the results in descending order. What I am having trouble figuring out is how to add a new column that identifies which table each count has come from.

I want my table to look something like:

So far this is what I’ve written:

CREATE TABLE MyProject.New_Table  
AS (  

SELECT  
 COUNT(DISTINCT Id) AS Number  
FROM 'MyProject.Table1'  
UNION ALL  

SELECT  
 COUNT(DISTINCT Id) AS Number  
FROM 'MyProject.Table2'  
UNION ALL  

SELECT  
 COUNT(DISTINCT Id) AS Number   
FROM 'MyProject.Table3'  
)  

ORDER BY Number DESC  

This gets me the Number column but not the table associated with it.

>Solution :

You need to add a new field to each of your subqueries as follows:

CREATE TABLE MyProject.New_Table AS

SELECT 'Table 1' AS Origin, 
       COUNT(DISTINCT Id) AS Number  
FROM 'MyProject.Table1'  

UNION ALL  

SELECT 'Table 2' AS Origin, 
       COUNT(DISTINCT Id) AS Number  
FROM 'MyProject.Table2'  

UNION ALL  

SELECT 'Table 3' AS Origin, 
       COUNT(DISTINCT Id) AS Number   
FROM 'MyProject.Table3'  

ORDER BY Number DESC  

Yet another option would be to first merge all your tables, and then apply aggregation once only:

CREATE TABLE MyProject.New_Table AS
SELECT Origin,
       COUNT(DISTINCT Id) AS Number
FROM (SELECT 'Table 1' AS Origin, Id FROM 'MyProject.Table1'  
      UNION ALL  
      SELECT 'Table 2' AS Origin, Id FROM 'MyProject.Table2'  
      UNION ALL  
      SELECT 'Table 3' AS Origin, Id FROM 'MyProject.Table3') cte
GROUP BY Origin