Surveillance tech: the CONSERVATIVE total (deduped, stated amounts only)

The only aggregate on this site that should be quoted. It keeps one row per duplicate group (`dup_rank` = 1), counts only figures actually stated as an amount (excludes "not to exceed" ceilings, proposed and recommended items), and drops rows flagged as anomalies or with suspect city attribution. It also excludes the non-surveillance ledgers (two-way radios, records/CAD software, fingerprint biometrics). Even this is a floor, not a precise number: it cannot tell an amendment from an original award.

Custom SQL query returning 1 row (hide) 

SELECT
  count(*)                             AS line_items,
  count(DISTINCT city || '|' || state_norm) AS governments,
  count(DISTINCT vendor)               AS vendors,
  round(sum(amount), 2)                AS conservative_total
FROM surveillance_spending
WHERE dup_rank = 1
  AND amount_type = 'stated'
  AND COALESCE(anomaly, 0) = 0
  AND COALESCE(geo_suspect, 0) = 0
  AND ledger NOT LIKE 'sv:radio_infra:%'
  AND ledger NOT LIKE 'sv:rms:%'
  AND ledger NOT LIKE 'sv:biometrics:%'
  AND ledger NOT LIKE 'sv:not_surveillance:%'
  AND ledger NOT LIKE 'sv:police_equipment:%'

Edit SQL

This data as json, CSV

line_itemsgovernmentsvendorsconservative_total
2486 853 122 784819652.92