Get percentage of record percentage in one query

Question

refer to this question:

Get the number of elements and their values ​​in one column

how can I get a percentage of the number of records in one query, for example:

ItemId count Percent ------------------------------------ 1 2 33.3 2 0 0 3 1 16.6 4 3 50.0 

thanks

Answer 1

COUNT(*) OVER() gives you the total.

Change In fact, you need SUM(COUNT(MyTbl.ItemID)) OVER() when you sum the values ​​in this column.

 SELECT Items.ItemID, [count] = COUNT(MyTbl.ItemID), [Percent] = 100.0 * COUNT(MyTbl.ItemID) / SUM(COUNT(MyTbl.ItemID)) OVER() FROM (VALUES (1,'N1'), (2,'N2'), (3,'N4'), (4,'N5')) Items(ItemID, ItemName) LEFT JOIN (VALUES(1), (1), (3), (4), (4), (4)) MyTbl(ItemID) ON ( MyTbl.ItemID = Items.ItemID ) GROUP BY Items.ItemID ORDER BY Items.ItemID 

Answer 2

 select ItemId, count(*) as count, cast(count(*) as decimal) / (select count(*) from myTable) as Percent from myTable group by ItemId 

Answer 3

 SELECT a.itemid , count(a.itemid) as [count] , ((cast(count(a.itemid) as decimal) / t.total) * 100) as percentage FROM table1 as a INNER JOIN (SELECT count(*) as total FROM table1) as t ON (1=1) GROUP BY a.item_id, t.total ORDER BY a.item_id 

Answer 4

 SELECT a.itemid, count(a,itemid) as [count], 100.00 * (count(a.itemid)/(Select sum(count(*) FROM myTable)) as [Percentage] FROM myTable Group by a.itemid Order by a.itemid