Surveillance tech: by state (conservative, deduped)

Custom SQL query returning 54 rows (hide)

SELECT state_norm AS state,
       count(*) AS line_items,
       count(DISTINCT city || '|' || state_norm) AS governments,
       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:%'
GROUP BY state
ORDER BY conservative_total DESC NULLS LAST

Edit SQL