Group by percentage of total in SQL Server

sql percentage of total sum
sql calculate percentage of column
sql calculate percentage of two columns
percentage in sql w3schools
sql calculate percentage discount
calculate percentage in sql server stored procedure
sql server get percent of total
sql percentage format

I have a SQL Table with a column of Items which I want to display the percentage of the total counts of each item in desc order.

For example

Column A
Item 1
Item 1
Item 2
Item 3

I would like to display:

Column A
Item 1   0.5
Item 2   0.25
Item 3   0.25

So far I have tried the following:

SELECT [Column A], (count([Column A]) / count(*))
  FROM [table]
  GROUP BY [Column A]
  Order by (count([Column A]) / count(*)) DESC

However, everything comes back as 1.

You can try to COUNT group by [Column A] colunm in main query. then make total count in subquery do division.

CREATE TABLE T(
   [Column A] VARCHAR(50)
); 

INSERT INTO T VALUES ('Item 1');
INSERT INTO T VALUES ('Item 1');
INSERT INTO T VALUES ('Item 2');
INSERT INTO T VALUES ('Item 3');

SELECT [Column A],COUNT(*) /CAST ((SELECT COUNT(*) FROM T) AS FLOAT)
FROM T 
GROUP BY [Column A]

sqlfiddle

Or you can use CROSS JOIN get the total then do division.

SELECT [Column A],COUNT(*) /CAST(total AS FLOAT)
FROM T CROSS JOIN (SELECT COUNT(*) total FROM T) v
GROUP BY [Column A],total

Calculating percentages with GROUP BY query, I would see if one of these or the other yields a better query plan with the appropriate tool for your RDBMS. N) / total.N as Pct from ( select User, Rating , count(*) as N from Results group by User In TSQL this should work SQL Recipes; Calculations Per Group; Calculating Percentage (%) of Total Sum; Calculating Percentage (%) of Total Sum in SQL. How to compute the ratio of a particular row to the sum of values? This question comes up frequently when you want to the relative contribution of a row against the backdrop of the total sum of all the rows. For example:

Your problem is that your aggregate is applied on the grouped column regardless, you will need to get the total separately in some way. One way would be to use a derived table as with @D-Shih. I am just going to store it in a variable.

The other problem you have is that both your operands are INT therefor SQL Server is inferring the datatype of INT for the result. you just need one of operands to not return an integer datatype. I have used an exact numeric datatype because approximate numeric's could cause problems if later you need to sum the individual % to get 100%.

DECLARE @t_item TABLE
(       name VARCHAR(10)
)

DECLARE @total_count NUMERIC(3,2)

INSERT INTO @t_item
VALUES ('ITEM1'),
       ('ITEM1'),
       ('ITEM2'),
       ('ITEM3'),
       ('ITEM3'),
       ('ITEM1')

SELECT @total_count = COUNT(*) FROM @t_item

SELECT name,
       COUNT(name),
       COUNT(*),
       pct_of_all      = CONVERT(NUMERIC(3,2),COUNT(name))/CONVERT(NUMERIC(3,2),COUNT(*))
  FROM @t_item
 GROUP BY name


SELECT name,
       COUNT(name),
       @total_count,
       pct_of_all      = COUNT(name)/@total_count
  FROM @t_item
 GROUP BY name

Calculating Percentage (%) of Total Sum · Advanced SQL · SILOTA, SQL Recipes; Calculations Per Group; Calculating Percentage (%) of Total Sum. Calculating Percentage (%) of Total Sum in SQL. How to compute the ratio of a  GROUP BY Course, Grade This gives me my totals by grade, but I am having trouble figuring out the percentage calculation in the query. Any help would be greatly appreciated.

I think Window functions are a better solution for this kind of case because the table is read once.

If instead of a single percentage you have to create a percentage, an average, and a sum? Or the query is more complex than a single count? Creating many subqueries is not an option. So, this is the reason I prefer window functions rather than subqueries.

dbfiddle

CREATE TABLE T(
   [Column A] VARCHAR(50)
); 

INSERT INTO T VALUES ('Item 1');
INSERT INTO T VALUES ('Item 1');
INSERT INTO T VALUES ('Item 2');
INSERT INTO T VALUES ('Item 3');

SELECT
   [Column A], CNT / SUM(CNT) OVER () PERC
FROM (
  SELECT [Column A], CAST(COUNT(*) AS NUMERIC) CNT
  FROM T 
  GROUP BY [Column A]
) X
ORDER BY PERC

Excel percentage formulas: Percentage of total, percent increase or , How do you find the percentage of a total in SQL? SELECT t1.sex, employed, count(*) AS `count`, count(*) / t2.total AS percent FROM my_table AS t1 JOIN ( SELECT sex, count(*) AS total FROM my_table GROUP BY sex ) AS t2 ON t1.sex = t2.sex GROUP BY t1.sex, employed;

Calculating "percentage of total" for each row with Postgres, can be done with a window function: SELECT *, (value / SUM(value) OVER ()) AS "% of total" FROM transactions WHERE quarter = '2015-03-31' and company_id = 1; We're using “OVER ()”, which means the sum over all rows returned by the where clause. I am always amazed at how SQL server provides a lot of calculations and logic built right into a select statement. Most of the time (if you are like me) you spend 85% of your time writing basic Select statements with a variety of where clauses. However last week I came across a need to get the percent of total for 1000’s of records.

SQL, How do you calculate the value of a SQL query? SQL > Advanced SQL > Percent To Total. To display percent to total in SQL, we want to leverage the ideas we used for rank/running total plus subquery. Different from what we saw in the SQL Subquery section, here we want to use the subquery as part of the SELECT. Let's use an example to illustrate. Say we have the following table, Table Total_Sales.

Calculate Percentage using GROUP BY – SQLServerCentral, SQL Percent To Total​​ The subquery SELECT SUM(Sales) FROM Total_Sales calculates the sum. We can then divide the individual values by this sum to obtain the percent to total for each row. The following Microsoft SQL Server T-SQL scripts illustrate the calculation of percent / percentage of on base total and on columns. The CONVERT function is used to format the percentage figures in percentage format. USE AdventureWorks2008; -- Calculate percent sql - SQL Server calculate percentage - sql convert percent.

Comments
  • That seemed to work. I had a question about the second solution though. Why is the v necessary for the query?
  • @notlaughing v is the alias name for the result set (SELECT COUNT(*) total FROM T)