Solved

using T-SQL to get count and percentage

Posted on 2011-02-17
3
735 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:rmtogether
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 167 total points
ID: 34922559
;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
 
LVL 3

Assisted Solution

by:sunezapa
sunezapa earned 167 total points
ID: 34922608
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 166 total points
ID: 34922657
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question