How do I convert the number of rows returned into a percentage?

by

Sure, this is quite easy:

DECLARE @total INT;

SELECT @total = COUNT(*) FROM <TableName>;

WITH [GroupedTable] AS (
    SELECT
       [Name] = <ColumnName>,
       [Count] = SUM(1)
    FROM
       <TableName>
    GROUP BY
       <ColumnName>
)
SELECT
    [Value] = [Name],
    [Percentage] = CAST([Count] AS decimal(19,10)) / @total * 100
FROM 
    [GroupedTable]
ORDER BY
    [Percentage] DESC

the ‘with’ just groups the table by the column, and counts the number of occurrences of that value. The second select just projects this data as a percentage. To get the percentage we just need to count up the total number of rows, which is the what the @total bit does. Hope this helps.

More Related Contents:

  1. convert sql query into lambda expression [closed]
  2. Store value in Yes/No field in Access using C#
  3. How to save leap date ’02/29/2006′ to sql server database? [duplicate]
  4. how to get date difference between two dates using DateDiff Excluding the weekends [duplicate]
  5. Is there a good LINQ way to do a cartesian product?
  6. Parsing SQL code in C# [closed]
  7. SQL Express connection string: mdf file location relative to application location
  8. LINQ to SQL – Left Outer Join with multiple join conditions
  9. Why does the Contains() operator degrade Entity Framework’s performance so dramatically?
  10. How to save image in database using C# [closed]
  11. Query extremely slow in code but fast in SSMS
  12. DateTime format to SQL format using C#
  13. Filter sql based on C# List instead of a filter table
  14. Code to validate SQL Scripts
  15. How to access ssis package variables inside script component
  16. SQLException : String or binary data would be truncated
  17. C# Version Of SQL LIKE
  18. C# SqlCommand – cannot use parameters for column names, how to resolve?
  19. Retrieve an object from entityframework without ONE field
  20. ALTER TABLE DROP COLUMN failed because one or more objects access this column
  21. Why is a SQL float different from a C# float
  22. When executing a stored procedure, what is the benefit of using CommandType.StoredProcedure versus using CommandType.Text?
  23. Oracle Parameters with IN statement?
  24. problem using Oracle parameters in SELECT IN
  25. How to insert data into SQL Server
  26. how to recognize similar words with difference in spelling
  27. Stored procedure returns int instead of result set
  28. How to use String property as primary key in Entity Framework
  29. Generating sql code programmatically
  30. Do we have transactions in MS-Access?

Leave a Comment