Create table using multiple table-CodePudding

I'm trying to create a table from multiple tables.

table a has list of IDs and I need count of IDs stored in table C.

Table b has list of IDs and need the count of them as well in table C.

I'm trying below but getting an error:

Create or replace tablec as 

select 
Count(id) as total
from table a,
select count(ref) as ref_total
from table b

My desired output should look like below and should be filter applied by date.

CodePudding user response：

Consider using a subquery for each count like below.

CREATE OR REPLACE TABLE TableC AS
SELECT (SELECT COUNT(id) FROM TableA) AS total,
       (SELECT COUNT(ref) FROM TableB) AS ref_total
;

output:

But date range is something optional. For date range, I'm thinking to get whole data in new table then I can run select on table C

I think you can add date filter in WHERE clause of each subquery for your purpose.