Surveillance tech: totals by technology category

Custom SQL query returning 17 rows (hide)

SELECT CASE WHEN ledger LIKE 'sv:%'
            THEN substr(ledger, 4, instr(substr(ledger,4), ':') - 1)
            ELSE ledger END AS category,
       count(*) AS line_items,
       count(DISTINCT vendor) AS vendors,
       count(DISTINCT city || state) 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 category
ORDER BY conservative_total DESC NULLS LAST

Edit SQL