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

Question

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

What does each row of table represent? Is each row a distinct event or is each row a distinct mention? Did you want to COUNT() the number of mentions or is mentions an INT64 that you want to SUM() instead? — baldwibr, Oct 28 '21 at 18:46

score 0 · Answer 1 · answered Nov 04 '21 at 00:12

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

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

1 Answers1