Administrators often have to calculate percentages of data that meet a condition. To make such calculations easier, I developed a solution that uses T-SQL. Here are a couple of examples that use the Northwind database to show how this code works.
Suppose you want to prepare a report that shows a percentage of sales to each customer for three product categories: beverages, confections, and others. You can use the code that Listing 2 shows to perform this calculation. The code groups all the data in Northwind by CompanyName and calculates a percentage by using a CASE statement inside a SUM() function.
The code in Listing 2 is simple. But let's look at a more complex example. Say you need a report that shows, for each customer, a percentage of sales of all the products in the three categories defined above. To accomplish this task, I rely on the fact that COUNT(*), COUNT(1), and SUM(1) are equal. By using a CASE statement inside a SUM() function, as Listing 3 shows, you can find the number of records that fall into one of the three specified categories. To calculate a percentage, you need to convert that number to a float value and divide by the total sales of all the products.
—Tomasz Borawski
tomaszborawski@yahoo.com