CODEWITHSUNDEEP
sqlgoogle-bigquery
SaiSivaSubramanian L
SaiSivaSubramanian L

Reputation: 159

BigQuery Querying Events Table

Objective : To take the users count which has event_name= 'Wallet'

Problem : I have limited the query's result to 100 to check so the expected result must be 100 but when I use count(params.value.string_value) it shows 124 .

Code : SELECT count(params.value.string_value) FROM "myproj.analytics_197163127.events_20190528",UNNEST(event_params) as params where event_name ='Wallet' and params.key = 'UserId' limit 100

Expected Result : if the query is returning 100 records the count should be 100 but how is it showing 124?

Hope the question is clear

Upvotes: 1

Views: 767

Answers (2)

ECris
ECris

Reputation: 563

The query shows 100 records because of the limit 100 at the end: 

SELECT event_date,event_timestamp,event_name, params.value.string_value 
FROM myproj.analytics_197163127.events_20190528, UNNEST(event_params) as params 
where event_name ='Wallet' and params.key = 'UserId' 
limit 100

Remove that and check again.

The LIMIT 100 specifies the number of rows to be returned from the SQL statement. It does not influence the COUNT() in your query. So there is a difference between : 

select count(*) from table limit 100

this will return a single value with the number of rows in the table. On the other hand : 

select count(*) from (select * from table limit 100)

This will return 100 (if the table has more than 100 rows - otherwise it will return the number of rows in table)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

limit is applied to the result set produced by the query.

Your query is an aggregation query with no group by. Such an aggregation always returns one row. So, the limit does not affect the results.

If you want to see 100 for the result set, use a CTE or subquery:

SELECT count(params.value.string_value)
FROM (SELECT params
      FROM "myproj.analytics_197163127.events_20190528" e CROSS JOIN
           UNNEST(e.event_params) params 
      WHERE e.event_name ='Wallet' AND params.key = 'UserId' 
      LIMIT 100
     ) ep

Upvotes: 2

Related Questions