Skip to content

Instantly share code, notes, and snippets.

@rchardptrsn

rchardptrsn/calculate statistics.sql

Created April 17, 2022 16:28
Show Gist options
  • Save rchardptrsn/1185a5f89c8b84043a23d6ba83944239 to your computer and use it in GitHub Desktop.
Save rchardptrsn/1185a5f89c8b84043a23d6ba83944239 to your computer and use it in GitHub Desktop.
Download ZIP
calculate statistics in postgresql
Raw
calculate statistics.sql
-- calculate statistics on the totals
SELECT
SUM(f.well_count) n_obs,
COUNT(f.well_count) n_counties,
ROUND(AVG(f.well_count),2) mean,
MIN(f.well_count) min,
MAX(f.well_count) max,
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY f.well_count) median,
ROUND(STDDEV(f.well_count),2) as stdev
-- calculate group totals
FROM (
SELECT COUNT(county) well_count
FROM fracking
GROUP BY county
) f
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment