New table with row counts

user_4900304 0
Viewed 62

I have 5 different tables stored in Hive, and I would like to know how to create a new table, called total_counts which has 5 columns each with the total row count from the individual tables. So, something like

enter image description here

My data is road flights for each year from 2015 to 2019, so I would like a table which just gives me the total number of accidents in each year.

I have tried variations of the following:

create table total_counts
as select COUNT(*)
from flights_2014 as "2014_count", flights_2015 as "2015_count;

I can get the counts for an individual year, but I can't seem to give the columns a heading, nor can I figure out how to do it for all my tables.

Thanks.

hiveql hive count sql
2 Answers

Calculate counts in sub-queries and do cross joins if you want to store data in columns

CREATE TABLE total_counts AS
SELECT 2015_count.cnt as 2015_count, 2016_count.cnt as 2016_count, ...
  FROM (SELECT COUNT(*) cnt FROM flights_2015) AS 2015_count
       CROSS JOIN
       (SELECT COUNT(*) cnt FROM flights_2016) AS 2016_count
       ...

Or the same using UNION ALL + aggregation:

CREATE TABLE total_counts AS
SELECT max(case when yr=2015 then cnt else 0 end) 2015_count, 
       max(case when yr=2016 then cnt else 0 end) 2016_count,      
       ...
  FROM (
       SELECT COUNT(*) cnt, 2015 yr FROM flights_2015
       UNION ALL
       SELECT COUNT(*) cnt, 2016 yr FROM flights_2016
       ...
       ) u
user_27003440 
CREATE TABLE total_counts AS
SELECT
    (SELECT COUNT(*) FROM flights_2015) AS 2015_count, 
    (SELECT COUNT(*) FROM flights_2016) AS 2016_count;

etc.

user_141657300
Related