6. Publications count per FoR category, total and percentage against total

Level: Advanced

This query requires a good understanding of SQL and the Dimensions data model

Description

Many publications in the Dimensions database are classified under ANZSRC Field of Research codes.

This query returns the total number of publications classified under each, plus the percentage of all publications in Dimensions with that classification.

Query

SELECT
  cat.name,
  COUNT(p.id) AS pubs_global,
  ROUND(
    (
      COUNT(p.id) * 100 /(
        SELECT COUNT(*)
        FROM `dimensions-ai.data_analytics.publications`
      )
    ), 2
  ) AS pubs_global_pc
FROM
  `dimensions-ai.data_analytics.publications` p,
  UNNEST(category_for.first_level.full) cat
GROUP BY
  cat.name
ORDER BY
  pubs_global_pc DESC,
  cat.name

Breaking it down

This query looks more complicated than it is. The main component could be summarized using this query:

SELECT
  cat.name,
  COUNT(p.id) AS pubs_global
FROM
  `dimensions-ai.data_analytics.publications` p,
  UNNEST(category_for.first_level.full) cat
GROUP BY
  cat.name

This query works exactly like the "publications per SDG" query, and there's a full breakdown there explaining how we unnest structs full of arrays full of structs. The main take-away is that we extract the names of all the first-level FOR codes, then count the number of publications listed under each one. That leaves only one more SELECT statement:

ROUND(
    (
      COUNT(p.id) * 100 /(
        SELECT COUNT(*)
        FROM `dimensions-ai.data_analytics.publications`
      )
    ), 2
  ) AS pubs_global_pc

This piece of the query just counts how many total records are in the publications table:

SELECT COUNT(*)
FROM `dimensions-ai.data_analytics.publications`

And this next piece of the query takes the total number of publications in a single classification and divides it by that total. The * 100 piece converts the decimal into a percentage:

COUNT(p.id) * 100 /(
  SELECT COUNT(*)
  FROM `dimensions-ai.data_analytics.publications`
)

The outer-most call, to the ROUND() function, states that the function should return the percentage rounded to two decimal places.

Results

[
  {
    "name": "Medical and Health Sciences",
    "pubs_global": "30145537",
    "pubs_global_pc": "25.49"
  },
  {
    "name": "Engineering",
    "pubs_global": "12388815",
    "pubs_global_pc": "10.48"
  },
  {
    "name": "Biological Sciences",
    "pubs_global": "9009961",
    "pubs_global_pc": "7.62"
  },
  {
    "name": "Chemical Sciences",
    "pubs_global": "7876669",
    "pubs_global_pc": "6.66"
  },
  {
    "name": "Physical Sciences",
    "pubs_global": "6149858",
    "pubs_global_pc": "5.2"
  },
  {
    "name": "Information and Computing Sciences",
    "pubs_global": "5236596",
    "pubs_global_pc": "4.43"
  },
  {
    "name": "Mathematical Sciences",
    "pubs_global": "5040655",
    "pubs_global_pc": "4.26"
  },
  {
    "name": "Psychology and Cognitive Sciences",
    "pubs_global": "3871992",
    "pubs_global_pc": "3.27"
  },
  {
    "name": "Studies in Human Society",
    "pubs_global": "3414299",
    "pubs_global_pc": "2.89"
  },
  {
    "name": "Language, Communication and Culture",
    "pubs_global": "2531296",
    "pubs_global_pc": "2.14"
  },
  {
    "name": "History and Archaeology",
    "pubs_global": "2357976",
    "pubs_global_pc": "1.99"
  },
  {
    "name": "Agricultural and Veterinary Sciences",
    "pubs_global": "2108659",
    "pubs_global_pc": "1.78"
  },
  {
    "name": "Earth Sciences",
    "pubs_global": "2059783",
    "pubs_global_pc": "1.74"
  },
  {
    "name": "Technology",
    "pubs_global": "1956256",
    "pubs_global_pc": "1.65"
  },
  {
    "name": "Commerce, Management, Tourism and Services",
    "pubs_global": "1830245",
    "pubs_global_pc": "1.55"
  },
  {
    "name": "Education",
    "pubs_global": "1838328",
    "pubs_global_pc": "1.55"
  },
  {
    "name": "Economics",
    "pubs_global": "1751713",
    "pubs_global_pc": "1.48"
  },
  {
    "name": "Philosophy and Religious Studies",
    "pubs_global": "1680088",
    "pubs_global_pc": "1.42"
  },
  {
    "name": "Environmental Sciences",
    "pubs_global": "1375226",
    "pubs_global_pc": "1.16"
  },
  {
    "name": "Law and Legal Studies",
    "pubs_global": "902366",
    "pubs_global_pc": "0.76"
  },
  {
    "name": "Studies in Creative Arts and Writing",
    "pubs_global": "644962",
    "pubs_global_pc": "0.55"
  },
  {
    "name": "Built Environment and Design",
    "pubs_global": "491404",
    "pubs_global_pc": "0.42"
  }
]