CODEWITHSUNDEEP
sqlgoogle-bigquery
linacarrillo
linacarrillo

Reputation: 79

SUM on a single column based on different tables on SQL

I have two tables, min_attribution and max_attribution which looks like this

session_id    attribution
1               search
2                 home

session_id    attribution
1               search
2                other

And here is the MRS

CREATE TABLE min_attribution
(session_id   INT, 
 attribution VARCHAR(20)
)

CREATE TABLE max_attribution
(session_id   INT, 
 attribution VARCHAR(20)
)

Insert into min_attribution values (1,'search')
Insert into min_attribution values (2,'home')
Insert into max_attribution values (1,'search')
Insert into max_attribution values (2,'other')

I am trying to write a query where, depending on the value of attribution, a score is given and added for each user ID. For example, if in the first table the value for attribution is search, add 40 and do the same with the other table, but adding 30. Expected output:

    session_id  search  home    other
         1        70     0        0
         2         0    40        30

What I did was trying to create a column for each of the possible attribution values (there are only a few) and add the results from each table, starting with "search", but it is not adding properly. This is my query

SELECT min_attribution.session_id, SUM(
        (CASE WHEN min_attribution.attribution = "search" THEN 40  ELSE 0 END) + 
        (CASE WHEN max_attribution.attribution = "search" THEN 30  ELSE 0 END)) search
FROM min_attribution,
     max_attribution
GROUP BY min_attribution.session_id

And the resulting table (current output, only for the search column):

session_id  search
1            110
2             30

Any ideas? ( I am using BigQuery)

Upvotes: 0

Views: 211

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you want union all:

select session_id,
       40 * countif(attribute = 'search'),
       40 * countif(attribute = 'home'),
       40 * countif(attribute = 'other')
from ((select session_id, attribution
       from min_attribution
      ) union all
      (select session_id, attribution
       from max_attribution
      )
     ) s
group by session_id;

Upvotes: 1

Related Questions