Finding % of Data

Question

This is what my table looks like

Measure    PT Name
Expired    Mickey Mouse
Expired    Donald Duck
Alive      Big Bird
Alive      Baby Bird
Rehab      Daffy Duck
Xray       Goofy

I want it to look like this:

Measure   Count  %
Expired   15     40%
Alive     5      13%
Rehab     7      18%
Xray      10     27%

I made a mistake, I want to get a count grouped by Measure including a % all in the same SQL statement

Select DischargeDisp, Count(*) as TotalCnt
INTO #Measure
from #LOS
group by DischargeDisp

I have the first part but I cannot seem to get the grouping of the Measure with the % — user3666224, Mar 27 '16 at 02:57
I also have the grouping by Measure but I want to include the % at the same time — user3666224, Mar 27 '16 at 02:58
I'm looking to see if there;s a way I can do it without 3 select statements — user3666224, Mar 27 '16 at 03:14
Did the question change significantly after some answers were posted? — MikeC, Mar 27 '16 at 14:55

Squirrel · Answer 1 · 2016-03-27T04:02:11.703

0

is this what you wanted ?

Select Measure, Count(*), Count(*) * 100 / SUM ( COUNT (*) ) OVER()
from   #LOS
group by Measure

edited Mar 27 '16 at 04:02

answered Mar 27 '16 at 03:33

Squirrel

23,507
4
34
32

This provided me the % but I want to also have a count of DischargeDisp – user3666224 Mar 27 '16 at 04:01
added count(*) to the query – Squirrel Mar 27 '16 at 04:02

score 0 · Answer 2 · answered Mar 27 '16 at 03:40

0

select Measure
      ,SUM(m_count) m_count
      ,(sum(m_count)*100/(select sum(m_count) from your_table)) as Percentage
      from your_table group by Measure;

Changed column name count to m_count. As it creates confusion.

answered Mar 27 '16 at 03:40

Ravi

30,829
42
119
173

I made a mistake, I don't have a column name m_count or count – user3666224 Mar 27 '16 at 03:58
I want to get the m_count and also the % – user3666224 Mar 27 '16 at 04:01

sqluser · Answer 3 · 2016-03-27T03:46:45.633

0

SELECT Measure,
       SUM(Cnt) AS [Count],
       CAST(SUM(Cnt) * 100 / (SELECT SUM(Cnt) FROM #LOS) AS VARCHAR(10)) + '%' AS [%]
FROM #LOS
GROUP BY Measure

edited Mar 27 '16 at 03:46

answered Mar 27 '16 at 03:41

sqluser

5,502
7
36
50

While this code may answer the question, providing additional context regarding *why* and/or *how* this code answers the question improves its long-term value. – Benjamin W. Mar 27 '16 at 04:57

score 0 · Answer 4 · answered Mar 27 '16 at 04:06

Your questions keeps changing but maybe this will meet your need:

Select Measure, Sum(1) as 'Count', LTrim(Str(Sum(1) * 100 / (Select Count(*) From Measure))) + '%' as [Percent]
  from Measure
  Group by Measure
order by Measure

score 0 · Answer 5 · answered Mar 27 '16 at 10:16

0

you can use this:

select distinct Measure, 
       count(*) over (partition by measure) as [Count],
       format(convert(float,count(*) over (partition by measure)) /count(*) over(),'p') as [%]
from los

test is here

answered Mar 27 '16 at 10:16

Vasily

5,707
3
19
34

Finding % of Data

5 Answers5