Page Numbers Without Tears

Why would you need to include number of pages in a report?

• physical collections inventory 
• planning collection shifts, especially when books are not located together and direct measurement (linear feet) is not feasible
• estimating the number of trucks needed to move materials to the Annex or another location
• doing tracers; height of volume and number of pages give an approximation of the size of the item being searched for
• counting the number of exposures needed to scan or copy a book

Where are page number data fields stored?

• in the MARC record, field 300, subfield a (the 300 field and the a subfield are both repeatable in a record). See Marc21 format for bibliographic data for details.
• page numbers are also extracted from the folio_inventory.instance table jsonb array and written to the instance_physical_descriptions derived table. See derivation code for this table.
• NOTE: Some electronic resources have a 300 field subfield a showing page numbers, if the original form of the work was print material

This example below shows 236 pages in the item, which is an electronic resources. If your query is only pulling items from a physical location, you should not get any electronic resources. However, it is good to be aware that the physical description may include

It is impossible to account for every variation of page number entry in your query

Basic Code Template for Page Numbers

Here is some code with explanatory comments to serve as a basic template for adding data showing the number of pages in the physical items you have selected in your query.

-- MARC 300 subfield a rows (can have multiple per instance)
number_of_pages_raw AS (
SELECT
m.instance_hrid,
m.content AS physical_description_300a,
-- numeric page count
CASE
WHEN substring(m.content from '\d{1,4}') IS NOT NULL
AND substring(m.content from '\d{1,4}')::int <= 3000
THEN substring(m.content from '\d{1,4}')::int
ELSE NULL
END AS page_count_estimate

FROM folio_source_record.marc__t AS m

LEFT JOIN folio_source_record.records_lb AS rec
ON m.instance_id = rec.external_id

WHERE m.field = '300'
AND m.sf = 'a'
AND rec.state = 'ACTUAL'

),


-- group to one row per instance_hrid

number_of_pages AS (
SELECT
instance_hrid,
MIN(physical_description_300a) AS physical_description_300a,
MIN(page_count_estimate) AS page_count_estimate
FROM number_of_pages_raw
GROUP BY instance_hrid
),