Benfords Law and Energy Data: The SQL

I had some folks ask me what SQL query that I used to calculate the first digit frequency distribution in my previous post on Benfords Law. I copied it from this nice post on detecting fraud with Benfords Law.

Benfords Law


select substring(value::text,1,1),count(*) from dad_data group by 1 order by 1;

Note that if you have negative numbers or positive floating point numbers less than 1.0, you will get frequencies for the “0” and “-” symbols included in the results.

P.S. Benford’s law, also called the first-digit law, is a phenomenological law about the frequency distribution of leading digits in many (but not all) real-life sets of numerical data. The law states that in many naturally occurring collections of numbers the small digits occur disproportionately often as leading significant digits.

Contact us