"INSERT INTO" an output table?

Question

I have a query that looks like this:

select hour, count(*)
from table
group by hour

I want the return to look like:

hour  count
8     1
9     0
10    3

But instead it looks like:

hour  count
8     1
10    3

This is because there were no entries in table for 9. The only solution I can think of is using an insert into statement wrapped around the query. So, something like:

insert into (
select hour, count(*)
from table
group by hour)
values (9, 0)

However this isn't proper syntax in Spark SQL.

Is it possible to insert into a table that's being generated as a result of a query without saving that table to a database? Is there an alternative way to accomplish what I want to accomplish?

how about union. select hour, count(*) from table group by hour union select 9 ,0 — Kostya
– Kostya, Commented Aug 2, 2016 at 17:30
Returns org.apache.spark.sql.AnalysisException: missing EOF at 'select' near 'union'; line 28 pos 0 — Danny David Leybzon
– Danny David Leybzon, Commented Aug 2, 2016 at 17:36
stackoverflow.com/a/1271845/5308100 See this answer. Should do the trick I believe. — Mark
– Mark, Commented Aug 2, 2016 at 18:30

Dan Bracuk · Accepted Answer · 2016-08-02 18:36:49Z

0

Something like this should work with any database engine:

select hour, sum(records) totalRecords
from (
select 0 hour, 0 records
from table
union
select 1 hour, 0 records
from table
...
union 
select 23 hour, 0 records
from table
union
select hour, count(*) records
from table
group by hour
) derivedTable

This assumes of course, that hour represents the hour of the day.

answered Aug 2, 2016 at 18:36

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

"INSERT INTO" an output table?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related