Build a Generic Histogram Generator for SQL Server

he histogram is a mathematical tool that is invaluable when analyzing large quantities of data. A histogram may be represented in a table of numbers or in a chart. Because databases tend to hold large quantities of data, it follows that a database interface that leverages histograms would be a useful tool to have in your analysis toolbox. That's exactly what you'll see here—how to generate complex histograms with a single line of T-SQL code. The T-SQL procedure provides tabular output within the confines of a DB query tool (e.g., SQL Server Management Studio or Query Analyzer). As just one example of a way to convert the tabular data to a graphical representation, you'll also see how to hook up the output of the stored procedure to the input of a chart within Excel.

Wikipedia's histogram overview defines a histogram as "a mapping. . . that counts the number of observations that fall into various disjoint categories." The Wikipedia article provides both a tabular and a graphical representation of a simple example: measuring the commute time for US workers. Table 1 shows a subset of the Wikipedia table that is the most relevant portion for this discussion. The first column is the list of disjoint categories, also referred to as bins or buckets. The second column shows the count of the workers (in thousands) corresponding to each bucket. From census data, the article author determined that 4.18 million people had commute times of 5 minutes or less; 13.687 million had commute times between 5 and 10 minutes; 18.618 million had commute times between 10 and 15 minutes; and so forth.