CODEWITHSUNDEEP
sqlgoogle-bigqueryview
Angela
Angela

Reputation: 43

Creating a view from another table

I want to create a view from another table..

Current view has the following content(image 1):

enter image description here

Trying to get an output from the above view as shown(image 2), i.e. grouping the values from those columns:

enter image description here

Upvotes: 3

Views: 82

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
SELECT bucket,
  COUNTIF(col = 0) AS w1,
  COUNTIF(col = 1) AS w2,
  COUNTIF(col = 2) AS w3,
  COUNTIF(col = 3) AS w4
FROM `project.dataset.your_table`, 
  UNNEST([w1, w2, w3, w4]) bucket WITH OFFSET col
GROUP BY bucket

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method is to unpivot the data and then re-aggregate:

select w,
       sum(case when i = 0 then 1 else 0 end) as w1,
       sum(case when i = 1 then 1 else 0 end) as w2,
       sum(case when i = 2 then 1 else 0 end) as w3,
       sum(case when i = 3 then 1 else 0 end) as w4
from (select array[w1, w2, w3, w4] ws
      from t
     ) t cross join
     unnest(ws) w with offset i
group by w;

Here is a more complete example:

with t as (
      select 1 as slno, '<5' as w1, '<5' as w2, '<5' as w3, '<5' as w4 union all
      select 1 as slno, '<5' as w1, '5 to 10' as w2, '<5' as w3, '5 to 10' as w4 
     )
select w,
       sum(case when i = 0 then 1 else 0 end) as w1,
       sum(case when i = 1 then 1 else 0 end) as w2,
       sum(case when i = 2 then 1 else 0 end) as w3,
       sum(case when i = 3 then 1 else 0 end) as w4
from (select array[w1, w2, w3, w4] as ws
      from t
     ) t cross join
     unnest(ws) w with offset i
group by w;

Upvotes: 2

Related Questions