Use the code below to query the count of forward citation, but it did't work

Question

I use the code below to query the count of forward citation, but it did't work.independent-tea-223707:Mypatent.publication_num_assignee is a table created by myself.

 SELECT c.publication_number AS Pub, COUNT(p.publication_number) AS
    CitedBy 
    FROM `patents-public-data.patents.publications` AS p, UNNEST(citation) AS c 
    WHERE c.publication_number IN  (SELECT publication_number FROM `independent-tea-223707:Mypatent.publication_num_assignee`)

Answer 1

This query is missing a GROUP BY() statement, as you are grouping the results by more than one column, while using COUNT(), which is an aggregate function.

Meanwhile, I invite you to review the the standard SQL syntax for BigQuery, you might want to particularly focus on the UNNEST statement.