CODEWITHSUNDEEP
sqlhivehiveql
jumpman8947
jumpman8947

Reputation: 581

SQL Hive add column based on column value

I have a query which looks like

select
  number,
  class,
   unix_timestamp(date1) - unix_timestamp(date2) as time_example,
   sum(unix_timestamp(date1) - unix_timestamp(date2)) over(partition by unix_timestamp(date1) - unix_timestamp(date2) order by class) as class_time
from myTable

Which gives results such as

number        class         time_example       class_time
1             math          5                  5
1             science       5                  10
1             art           5                  15
1             math          2                  2
1             science       2                  4
1             art           2                  6
1             math          10                 10
1             science       10                 20
1             art           10                 30

I want to add columns based on class, and only have 3 different columns because there are only 3 columns. For example the time for math and would get 17. This is the desired table i would like to get

number        class         class_time
1             math          17
1             science       17
1             art           17

Upvotes: 0

Views: 129

Answers (1)

Prabhat Ratnala
Prabhat Ratnala

Reputation: 705

You can do that by using group by

select number, class,
sum(unix_timestamp(date1) - unix_timestamp(date2)) as class_time
from myTable
group by number,class;

Upvotes: 1

Related Questions