Article ID: 256282 - View products that this article applies to.
This article was previously published under Q256282
T-SQL does not contain a built in aggregate function that you can use directly in a grouping query to compute and display the percentage of the total number of rows aggregated in each group of records generated by the GROUP BY clause.
For example, in the SQL Server Pubs sample database it is possible to write a T-SQL GROUP BY query that displays the authors and the total number of titles written by each by using the COUNT aggregate function. However, you cannot calculate the percentage of the total number of titles written by each author by using any of the existing numeric aggregate T-SQL functions directly in the column list of the SELECT statement.
This article illustrates how to write a T-SQL GROUP BY query to compute and display the percentage of total records aggregated in each group by using a nested SELECT statement and the T-SQL CONVERT function.
The following example is based on the TitleAuthor table in the SQL Server 7.0 Pubs sample database.
To obtain a list of author IDs and the total number of titles written by each author based on the data available in the TitleAuthor table, you execute a T-SQL query identical to this:
To get a list of author IDs and the percentage of total titles written by each author requires a more complex SQL statement as there is no T-SQL aggregate function that you can use to compute the percentage of rows contained in each group of records. The following SQL SELECT statement demonstrates how you can accomplish this by using a nested query and the T-SQL CONVERT function:
Here is a review of how the query works.
To calculate the percentage of total records contained within a group is a simple result that you can compute. Divide the number of records aggregated in the group by the total number of records in the table, and then multiply the result by 100. This is exactly what the preceding query does. These points explain the query in greater detail: