3

I want to calculate the sum of the same ids and display in each record. expected Sample is given below

Expectd result

leftjoin
  • 28,302
  • 6
  • 46
  • 84
Gouri Mahapatra
  • 93
  • 8
  • Does this answer your question? [Is it possible to use Aggregate function in a Select statment without using Group By clause?](https://stackoverflow.com/questions/6467216/is-it-possible-to-use-aggregate-function-in-a-select-statment-without-using-grou) – yahoo Oct 13 '20 at 10:53

3 Answers3

2

Use analytics SUM():

SELECT
      Code, SUM(Value) OVER (PARTITION BY Code) as Value
  FROM mytable;
leftjoin
  • 28,302
  • 6
  • 46
  • 84
1

Summing over the window should allow you to maintain the overall sum for each record. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures that all values are taken into consideration

SELECT
   Code,
   SUM(Value) OVER (PARTITION BY Code ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM
  mytable;
ggordon
  • 3,836
  • 10
  • 19
  • The `rows` component is redundant and typically left out. That is why leftjoin's answer is preferred. – Gordon Linoff Oct 13 '20 at 12:23
  • @GordonLinoff Thanks for this. I will have to check again with my current version of hive. Another user faced similar difficulties https://stackoverflow.com/questions/25082057/hive-sum-over-a-specified-group-hiveql but I can investigate more – ggordon Oct 13 '20 at 15:06
-1

select id,sum(value) from yourtablename group by code

Chameera
  • 326
  • 2
  • 8