CODEWITHSUNDEEP
postgresqlgoogle-bigquery
Tudor
Tudor

Reputation: 4164

How to query table and sum up certain columns by criteria, but not others?

From a starting table, let's say:

A B C
1 1 99
2 2 88
3 3 77

I'm trying to write a query that would result in a table with a different value in column C based on the criteria that when A has value 2, the value for C should be the existing value + the value from C where A is 1. Here's the result:

A B C
1 1 99
2 2 187
3 3 77

Unsure if a grouping makes sense here, especially since there might be multiple similar criteria. The closes query I could think of would be

SELECT A, B, C+(SELECT C FROM table1 WHERE A=1 LIMIT 1) FROM table1 WHERE A=2;

but this isn't valid SQL, since subqueries can't be used like this. Any suggestions are welcome, even if they involve somehow altering the structure of the original table.

Upvotes: 0

Views: 65

Answers (2)

MAM
MAM

Reputation: 101

SELECT
    A,
    B,
    CASE
        WHEN A=2 THEN C + (SELECT C FROM table WHERE A = 1)
        ELSE C
    END AS C
FROM
    table;

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

consider below approach (tested in BigQuery)

select a, b, c + 
  case a 
    when 2 then sum(if(a = 1, c, 0)) over()
    else 0
  end c
from your_table

if applied to sample data in your question - output is

enter image description here

Upvotes: 2

Related Questions