CODEWITHSUNDEEP
sql
itsMe
itsMe

Reputation: 785

How to do SUM based on SELECT DISTINCT on multiple column

I have a table where there are 3 numeric columns (a,b,c) and two categorical column (x,y). I want make a sum for a ,b and c but based on each unique x and y combination. How can I do that ? I have tried the following code

SELECT   
SUM(CAST(t.a as INT64)) as a_,
SUM(CAST(t.b  as INT64)) as b_ ,
SUM(CAST(t.c  as INT64)) as c_,

FROM (
  SELECT DISTINCT x ,y FROM <table_name>  
) t

Now what I am getting is a not found insde t

How can I fix that ? what I am doing wrong ?

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Why are you casting the values if they are already numbers? I think you want:

SELECT x, y, SUM(a) as sum_a, SUM(b) as sum_b, SUM(c) as sum_c
FROM t
GROUP BY x, y;

If you want the sum of all three (which your question suggests, then you want:

SUM(a + b + c) as sum_abc,

or:

SUM(a) + SUM(b) + SUM(c) as sum_abc

These last two are subtly different, if any of the values are NULL. It is not clear which you might want.

Upvotes: 0

erbg
erbg

Reputation: 346

You need to use a group by in this case 

SELECT   
x,
y,
SUM(CAST(t.a as INT64)) as a_,
SUM(CAST(t.b  as INT64)) as b_ ,
SUM(CAST(t.c  as INT64)) as c_

from
<table_name>  
group by x, y

Upvotes: 2

Related Questions