Article ID: 256282
Article Last Modified on 7/15/2004
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q256282
SUMMARY
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.
MORE INFORMATION
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:
Select au_id, count(title_id) from TitleAuthor group by au_id
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:
select au_id,(convert(numeric(5,2),count(title_id))/(Select convert(numeric(5,2),count(title_id)) from titleauthor)) * 100
AS "Percentage Of Total Titles"
from titleauthor group by au_id
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:
- The inner nested query returns the total number of records in the TitleAuthor table:
[ Select convert(numeric(5,2),count(title_id)) from titleauthor ]
- The value returned by the COUNT(title_id) in the outer GROUP BY query returns the number of titles written by a specific author.
- The value returned in step 2 is divided by the value returned in step 1, and the result is multiplied by 100 to compute and display the percentage of the total number of titles written by each author.
- The nested SELECT is executed once for each row returned by the outer GROUP BY query
- The CONVERT function is used to cast the values returned by the COUNT aggregate function to the numeric data type with a precision of 5 and a scale of 3 to achieve the required level of precision.
Additional query words: TSQL, SQL SERVER, PERCENTAGE, GROUP BY
Keywords: kbhowto KB256282
