CODEWITHSUNDEEP
mysqlgoogle-analyticsgoogle-bigquery
Kshitij Marwah
Kshitij Marwah

Reputation: 1151

Find the Count of a property for a particular condition in Table in Big Query

I have a table which has many properties in Big Query. I am concerned with two properties of the Big Query.

I have

hits.eCommerceAction.action_type , hits.product.productSKU and hits.hitNumber.

Have written the following code 

SELECT hits.product.productSKU as SKU,
IF(hits.eCommerceAction.action_type = '1', (hits.hitNumber), NULL) ListCLicks 
IF(hits.eCommerceAction.action_type = '2', (hits.hitNumber), NULL)) PDV
FROM [107485206.ga_sessions_20160101]
where hits.eCommerceAction.action_type in ('1')
#group by SKU,ListClicks

My problem is that the above code returns the first value of hits.hitNumber which is a index number for a SKU. A SKU can have multiple hits.hitNumber. I want to count(not sum as it a index) the total hits.hitNumber for SKU.

ProductSKU | PDV | ListCLicks
-------------------------
1          | 120 |  235 
2          | 234 |  124 
3          | 2311|  1256
4          | 12  |  34
5          | 12  |  33
2          | 112 |  345
4          | 789 |  1110
2          | 3333|  2131

PDV is hits.hitNumber index when hits.eCommerceAction.action_type = '2'and List Clicks is hits.hitNumber index when hits.eCommerceAction.action_type = '1'

And the Output is 

ProductSKU | PDV | ListCLicks
-------------------------
1          | 1   |  1   
2          | 3   |  3   
3          | 1   |  1
4          | 2   |  2
5          | 1   |  1

PDV is hits.hitNumber count when hits.eCommerceAction.action_type = '2'and List Clicks is hits.hitNumber count when hits.eCommerceAction.action_type = '1'

How can I do this?

Upvotes: 0

Views: 214

Answers (1)

Danny Kitt
Danny Kitt

Reputation: 3251

I am assuming you want to count the number of ecommerce actions by type. If so, you can accomplish this with a SUM that adds 1 per relevant action, and 0 otherwise:

SELECT hits.product.productSKU as SKU,
  SUM(IF(hits.eCommerceAction.action_type = '1', 1, 0)) ListCLicks, 
  SUM(IF(hits.eCommerceAction.action_type = '2', 1, 0)) PDV
FROM [107485206.ga_sessions_20160101]
GROUP BY SKU

Upvotes: 1

Related Questions