CODEWITHSUNDEEP
google-bigquery
veena989798
veena989798

Reputation: 31

how to find the sum for an array of struct type?

how to find the sum of 2015th first quarterly?

with table1 as(
select [struct("jan" as month,2000 as quantity_sold),("feb",5000),("mar",3000)] as Q1_2015,
 [struct("jan" as month,2050 as quantity_sold),("feb",5100),("mar",3200)]as Q1_2016,
 [struct("jan" as month,3000 as quantity_sold),("feb",4000),("mar",3800)]as Q1_2017)
 ,
table2 as(
select(array(select x from unnest(Q1_2015)x order by quantity_sold desc))as tab2_1
from table1)

select tab2_1, sum(tab2_1.quantity_sold)   from table2;

I have the following error

Error: Cannot access field quantity_sold on a value with type ARRAY<STRUCT<month STRING, quantity_sold INT64>> at [10:19]

Upvotes: 0

Views: 1727

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173106

First of all - without GROUP BY your final SELECT will not work anyway - and will fail with something as below 

Error: SELECT list expression references column tab2_1 which is neither grouped nor aggregated

Meantime, to fix your current issue with below error: 

Error: Cannot access field quantity_sold on a value with type ARRAY<STRUCT<month STRING, quantity_sold INT64>> at [10:19]

you should use something like below 

#standardSQL
WITH table1 AS(
  SELECT 
    [STRUCT("jan" AS month,2000 AS quantity_sold),("feb",5000),("mar",3000)] AS Q1_2015,
    [STRUCT("jan" AS month,2050 AS quantity_sold),("feb",5100),("mar",3200)]AS Q1_2016,
    [STRUCT("jan" AS month,3000 AS quantity_sold),("feb",4000),("mar",3800)]AS Q1_2017
), table2 AS(
SELECT
  (ARRAY(SELECT x FROM UNNEST(Q1_2015) x ORDER BY quantity_sold DESC)) AS tab2_1
  FROM table1
)
SELECT SUM(tab2_1[OFFSET(0)].quantity_sold)   
FROM table2

Above just addresses you issue with error you presented in question - the rest of logic is exactly as it is in your question and I don't think result is what you expected - most likely it is just start of your "journey"

Anyway - to produce the sum of three months - use below instead 

#standardSQL
WITH table1 AS(
  SELECT 
    [STRUCT("jan" AS month,2000 AS quantity_sold),("feb",5000),("mar",3000)] AS Q1_2015,
    [STRUCT("jan" AS month,2050 AS quantity_sold),("feb",5100),("mar",3200)]AS Q1_2016,
    [STRUCT("jan" AS month,3000 AS quantity_sold),("feb",4000),("mar",3800)]AS Q1_2017
), table2 AS(
SELECT
  (SELECT SUM(quantity_sold) FROM UNNEST(Q1_2015)) AS total_sold
  FROM table1
)
SELECT total_sold   
FROM table2

I consider this particular question is answered - please post any new questions as "New Questions"

Upvotes: 1

Related Questions