CODEWITHSUNDEEP
google-bigquerygdelt
janeluyip
janeluyip

Reputation: 17

bigquery and gdelt database: how to count the number of event and number of mentions

Is count(*) code the number of event. But in my data the are the same as count(NumMentions) as Mentions, In fact, mentions may be more than events. what's wrong with my code

SELECT
YEAR,
ACTOR1COUNTRYCODE as Country1,
Actor2CountryCode as Country2,
count(NumMentions) as Mentions,
COUNT(*),
From gdelt-bq.full.events_partitioned
Where
_PARTITIONTIME BETWEEN TIMESTAMP('2012-01-01') AND TIMESTAMP('2013-12-31')
AND ACTOR2COUNTRYCODE='CHN'
GROUP BY YEAR,Country1,Country2
ORDER BY  YEAR,Country1,Country2

Upvotes: 0

Views: 220

Answers (1)

baldwibr
baldwibr

Reputation: 209

Sounds like you just need to change your COUNT(NumMentions) to SUM(NumMentions). COUNT() will count the number of records, but SUM() will actually sum the integers.

SELECT
  YEAR,
  ACTOR1COUNTRYCODE as Country1,
  Actor2CountryCode as Country2,
  SUM(NumMentions) as Mentions,
  COUNT(*)
From gdelt-bq.full.events_partitioned
Where
  _PARTITIONTIME BETWEEN TIMESTAMP('2012-01-01') AND TIMESTAMP('2013-12-31')
    AND ACTOR2COUNTRYCODE='CHN'
GROUP BY YEAR,Country1,Country2
ORDER BY  YEAR,Country1,Country2

Upvotes: 0

Related Questions