using T-SQL to get count and percentage

Hi, experts

I use a SQL code like below

SELECT DATEPART(weekday, From_date) as week_day, COUNT (*) as CNT
FROM  table_A
group by DATEPART(weekday, From_date)
order by DATEPART(weekday, From_date)

it give me
week_day      CNT
1      2
2      3
4      5
5      7
6      2
7      10

How can I add the percentage in the 3rd column. so my result will be

week_day      CNT   Percent
1      2         2 / (2+3+5+7+2+10)
2      3        3 / (2+3+5+7+2+10)
4      5        5 / (2+3+5+7+2+10)
5      7        7 / (2+3+5+7+2+10)
6      2         2 / (2+3+5+7+2+10)
7      10       10 / (2+3+5+7+2+10)

Is there a simply way to do by T-SQL

rmtogetherAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ThomasianConnect With a Mentor Commented:
You could use a combination of SUM OVER and COUNT to get the total count.

SELECT DATEPART(weekday, From_date) as week_day, COUNT (*) as CNT
     ,100.0 * COUNT(*) / SUM(COUNT(*)) OVER () [Percent]
FROM  table_A
group by DATEPART(weekday, From_date)
order by DATEPART(weekday, From_date)
0
 
JoeNuvoConnect With a Mentor Commented:
;with CTE AS ( 
SELECT DATEPART(weekday, From_date) as week_day, COUNT (*) as CNT
FROM  table_A
group by DATEPART(weekday, From_date)
 )
SELECT
	week_day, CNT, CAST(CNT * 100 as float) / (select sum CNT) from CTE) as percentage
FROM CTE
ORDER BY week_day

Open in new window

0
 
sunezapaConnect With a Mentor Commented:
for easy of reading I used "weekday" for DATAEPART etc.

 DECLARE @Totals as int;
	set @totals  = (SELECT COUNT(weekday)  FROM  dbo._Test);
	SELECT     weekday, COUNT(x) AS cnt, 100*COUNT(xxx)/@Totals as prcnt
	FROM         dbo._Test AS _Test_1
	GROUP BY weekday

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.