Get percent of an interval using single query

Question

Refer to my previous Questions:

Get count of items and their values in one column

Get count percent of a record in a single query

How I can get percentage of intervals? such this example:

  ItemId        count          Percent 
 ------------------------------------    
   1-2            2              33.3    
   3-4            4              66.6

thanks

Use a subquery. The subquery would sum all of the count column which you can use to compute the percentage based on the count of each individual item/row. — mservidio, Sep 10 '11 at 03:59
Or first store the total sum into a local variable, and then write a regular query to divide the number with the count per each row. — mservidio, Sep 10 '11 at 04:00
user ask for some interval such : 1-2 , 3-4 , 4-5 , more that 5 — Arian, Sep 10 '11 at 07:33

score 3 · Accepted Answer · answered Sep 10 '11 at 10:41

Your Intervals table could be a TVP in SQL Server 2008.

SELECT Intervals.ItemId,
       [count] = COUNT(MyTbl.ItemID),
       [Percent] = 100.0 * COUNT(MyTbl.ItemID) / SUM(COUNT(MyTbl.ItemID)) OVER()
FROM   (VALUES(NULL,0, 'Less than 1'),
              (1,2,'1-2'),
              (3,4,'3-4'),
              (6,NULL,'More than 4')) Intervals (Low, High, ItemId)
       LEFT JOIN (VALUES(1),
                        (1),
                        (3),
                        (4),
                        (4),
                        (4)) MyTbl(ItemID)
         ON ( MyTbl.ItemID BETWEEN ISNULL(Intervals.Low, -2147483648) AND
                                        ISNULL(Intervals.High, 2147483647) )
GROUP  BY Intervals.ItemId,
          Intervals.Low
ORDER  BY Intervals.Low

score -1 · Answer 2 · answered Sep 10 '11 at 03:57

-1

Try this

select itemId,count(*),(count(*)/xx.Tot)*100 as Percent
from tableName a
join (select sum(count) as Tot from tableName) xx on 1=1
group by ItemID

answered Sep 10 '11 at 03:57

Sparky

14,967
2
31
45

please first see my refered links – Arian Sep 10 '11 at 04:20
Sorry, missed the interval part. You can do that with a case statement, but it looks like you've already found a solution... – Sparky Sep 10 '11 at 15:31

Get percent of an interval using single query

2 Answers2