# 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

###### Who is Participating?

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

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
``````
0

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
``````
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.