GROUP BY and division-CodePudding

In GBQ table i have structure(page_location, event_name, count) event_name can have value 'view', 'add_basket'. I want receive structure - GROUP BY(page_location), (value count from event_name = 'add_basket' / value count from event_name = 'view'). I tried PIVOT and OVER am already confused how to do it.

There is

page_location, event_name, count
A, view, 100
A, add_basket, 10
A, view, 200
A, add_basket, 20`

Necessary

page_location, count
A, 0.1
B, 0.1

My table

CodePudding user response：

You can combine function sum and case when to solve your problem. My solution is following:

select page_location
,sum(case when event_name = 'add_basket' then count else 0 end) 
/ sum(case when event_name = 'view' then count else 0 end) end as ratio

from gbq

group by page_location

CodePudding user response：

Try this

with cte as
(


select 'A' as pl, 'view' as event_name, 100 as count UNION ALL
select 'A' as pl, 'add_basket', 10                   UNION ALL
select 'A' as pl, 'view', 200                        UNION ALL
select 'A' as pl, 'add_basket', 20                   UNION ALL 

select 'B' as pl, 'view', 200                        UNION ALL
select 'B' as pl, 'view', 200                        UNION ALL
select 'B' as pl, 'view', 200                        UNION ALL
select 'B' as pl, 'view', 200                        

)
select pl,sum(add_basket)/sum(view)
from cte
pivot(sum(count) for event_name in ('view','add_basket'))
group by pl