CODEWITHSUNDEEP
google-bigquery
Álvaro De Andrés
Álvaro De Andrés

Reputation: 3

Adding values with condition in google bigquery

I need to add some values with a condition in GoogleBigQuery

NOTICE: I edited the original question since it was not accurate enough.

Thanks to the two participants who have tried to help me. I tried to apply the solutions kindly suggested by you but I got the same result from the pct column as a result.

Something like this: results

Here is the more detailed definition:

TABLE

Columns:

Shop: Shop location

brand: Brands of cars sold at shoplocation

sales: sales of each brand sold at each shop_location rank: Rank of each brand per shop location (the biggest the greater)

total_sales_shop: SUM of all brand sales per shop location

pct: percentage of sales by brand in relationship with shop location pct_acc:

What i need to calc is pct_acc which is the cumulative sum of the shops by rank (while it has no relation with brand)

PCT_ACC

My need is to reach something like PCT_ACC, and then save the results in another one like this:endtable

Upvotes: 0

Views: 705

Answers (2)

Sandesh
Sandesh

Reputation: 438

You can use following query to get the required data:

select values, rank, 
sum(case when rank<2 then values else 0 end) condition1

from table
group by values, rank

Need to add/remove columns from select and group by as per requirement

To get the cumulative sum you can use following query:

select shop, brand, sales, rank, total_sales_shop, pct , 
sum(pct) over (partition by shop order by rank) as pct_act
from data

And to get the final table you can use combination of case statement and group by

e.g

select shop,
max(case when rank=1 then pct_act end) as rank_1,
max(case when rank=2 then pct_act end) as rank_2,
max(case when rank=3 then pct_act end) as rank_3,
max(case when rank=4 then pct_act end) as rank_4,
max(case when rank=5 then pct_act end) as rank_5
from cumulative_sum
group by shop

Upvotes: 1

rmesteves
rmesteves

Reputation: 4085

If you want only the final sum for the rows with that condition you can try:

SELECT  
   SUM (IF(Rank < 2, Values, 0) AS condition1 
FROM table

If you want to get the rank and the sum only for the rows with that condition you can try doing

SELECT  
   Rank,
   SUM (IF(Rank < 2, Values, 0) AS condition1 
FROM table
WHERE
   RANK < 2
GROUP BY  
   Rank

Finally, if you want to get the rank and the sum considering all the rows you can try doing:

SELECT  
   Rank,
   SUM (IF(Rank < 2, Values, 0) AS condition1 
FROM table
GROUP BY  
   Rank

I hope it helps

Upvotes: 0

Related Questions