Add up factors across libraries

Add only lines whose factor has gwp_normalization_basis <> 'unconverted', multiply by value_co2e_normalized, and report the lines you dropped. In a three-line inventory priced with DESNZ and MITECO factors, two lines add up to 13,233.5 kg CO2e on AR6 and one of the three is left out.

The query#

The inventory is inline here; in practice it is your own table joined on slug.

WITH inventory (line, slug, quantity) AS (
  VALUES
    (1, 'ef-desnz-cars_by_market_segment-gb-km-grid_supplied-ffb3c31e', 12000),
    (2, 'ef-miterd-electricidad_mix_sin_gdo-es-kwh-generation-c0551d67', 50000),
    (3, 'ef-desnz-gaseous_fuels_butane-gb-l-well_to_wheel-a9f7b17d', 500)
), priced AS (
  SELECT i.line, f.library, f.reference_year, f.unit_code,
         f.gwp_method_native, f.gwp_normalization_basis,
         CASE WHEN f.gwp_normalization_basis <> 'unconverted'
              THEN i.quantity * f.value_co2e_normalized END AS kg_co2e_ar6
  FROM inventory i
  LEFT JOIN open_ef.factors_flat f
    ON f.slug = i.slug AND f.is_default_indicator AND f.is_latest_activity_year
)
SELECT line, library, reference_year, unit_code, gwp_method_native, gwp_normalization_basis,
       round(kg_co2e_ar6::numeric, 1) AS kg_co2e_ar6,
       round((sum(kg_co2e_ar6) OVER ())::numeric, 1) AS total_added,
       count(*) FILTER (WHERE kg_co2e_ar6 IS NULL) OVER ()
         || ' of ' || count(*) OVER () AS lines_dropped
FROM priced
ORDER BY line;

Line 1 is 12,000 km in a lower-medium battery electric car, priced with the row DESNZ prints for the electricity it draws: generation plus the network losses, with the upstream of that electricity left out. Line 2 is 50,000 kWh of Spanish residual-mix electricity. Line 3 is 500 litres of butane well to use, which for a fuel is the burn plus its upstream.

Each line is pinned by slug, not by activity name. One activity can carry several rows that pass the two default filters, more than one of them a root, so an activity name plus a boundary does not identify a single value; boundary_profile on the row you picked is what says which parts are in it. The upstream of line 1's electricity is a separate DESNZ row that this inventory does not price. It is printed as a bare CO2e total, so it is unconverted: had it been a line, the filter would have dropped it rather than adding it. A part you leave out of the inventory is counted nowhere, not even in lines_dropped, so give it a line of its own when you want it reported.

How to read it#

• native: the publisher already used AR6, so the value is copied as is (line 2).
• recharacterized: the publisher used an older GWP set but printed the gases separately, so each gas was re-weighted to AR6 (line 1).
• invariant: the value is pure CO2, which weighs 1 in every GWP set.
• unconverted: the conversion cannot be done exactly, for example because part of the value was printed as a CO2e total with no gases (line 3, whose upstream part is a bare total). value_co2e_normalized then holds the native AR5 number, and adding it to AR6 numbers mixes two scales.

A LEFT JOIN keeps a line whose slug matches no row, so a missing factor shows up as a NULL line too. Count it with the dropped lines.

reference_year is the year the publisher attaches to the value, a data year for some libraries and the edition year for others (DESNZ prints an edition year here, MITECO a data year), and is_latest_activity_year picks the latest year that activity carries: 2026 for the two DESNZ rows, 2025 for the MITECO one. That is a per-activity pick, not the newest year in the library, which can hold later rows for activities you did not price. A cross-library total is normally a mix of years, so report the years beside it. See Reference year.

Watch out#

Watch out. Do not filter on gwp_method_normalized = 'ar6'. An AR6 row on a 20-year or 500-year horizon passes that test and answers a different question. gwp_normalization_basis <> 'unconverted' is the one filter that guarantees AR6 on a 100-year horizon.

Watch out. The sum adds one row per line and never checks that your lines are disjoint. DESNZ publishes the burn and the upstream as rows of their own and prints no total, so open_ef sums the two into line 3's well-to-use parent and marks it is_derived. An inventory holding that total and one of its parts counts that part twice, with no error message. is_top_parent marks a row with no parent, so it is never a slice of another row in factor search. That is not a proof of disjointness on its own: a part the publisher prints flat, like the upstream of line 1's electricity, has no parent either. See Check that a decomposition sums to its parent.

Watch out. A dropped line is not zero emissions. Report lines_dropped beside the total, then price those lines another way: a factor from a library that converts, or a separate subtotal on the native GWP set that you label as such.

Why it works. Libraries publish on different GWP sets, and some rows do not name the set the publisher used at all. Summing their native values adds numbers on different scales without any error message. The normalized column puts every convertible value on AR6 GWP100 and labels the rest, so the filter, not your judgement, decides what may be added.

Related#

• Global warming potentials
• Glossary: native GWP and normalised GWP
• Calculate emissions for a quantity
• Guarantees you can rely on