CODEWITHSUNDEEP
google-bigquery
Timogavk
Timogavk

Reputation: 889

How to get all sums values with out each element using BigQuery?

I have a table in BigQuery. I want to count all sums of values in column removing each element alternately by id. As output I want to see removed id and sum of other values.

WITH t as (SELECT  1 AS id, "LY" as code,  34 AS value
UNION ALL
SELECT  2, "LY",  45
UNION ALL
SELECT  3, "LY", 23
UNION ALL
SELECT  4, "LY", 5
UNION ALL
SELECT  5, "LY", 54
UNION ALL
SELECT  6, "LY", 78)
SELECT lv id, SUM(lag) sum_wo_id
FROM 
(SELECT *, FIRST_VALUE(id) OVER (ORDER BY id DESC) lv, LAG(value) OVER (Order by id) lag from t)
     GROUP BY lv

In example above I can see sum of values with out id = 6. How can I modify this query to get sums without another ids like 12346, 12356, 12456, 13456, 23456 and see which one removed?

Upvotes: 1

Views: 41

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173106

Below is for BigQuery Standard SQL

Assuming ids are distinct - you can simply use below

#standardSQL
SELECT id AS removed_id, 
  SUM(value) OVER() - value AS sum_wo_id
FROM t

if applied to sample data from your question - output is

Row removed_id  sum_wo_id    
1   1           205  
2   2           194  
3   3           216  
4   4           234  
5   5           185  
6   6           161

In case if id is not unique - you can first group by id as in below example

#standardSQL
SELECT id AS removed_id, 
  SUM(value) OVER() - value AS sum_wo_id
FROM (
  SELECT id, SUM(value) AS value
  FROM t
  GROUP BY id
)

Upvotes: 1

Related Questions