Sum & Percentage of Count in sql server 2005

Question

1.00/5 (1 vote)

See more:

I have following scenario

original format

SrNo Month Issues
1 January 8
2 February 14
3 March 26
4 April 21
5 May 14
6 June 19
7 July 2

I need following format in sql

Sr.No.Month Issues SUM %
1 January 8 104 7.69
2 February 14 104 13.46
3 March 26 104 25.92
4 April 21 104 20.19
5 May 14 104 13.46
6 June 19 104 18.32
7 July 2 104 0.96

Posted 7-Jul-12 1:56am

Kailash_Singh

Add a Solution

Comments

[no name] 7-Jul-12 8:02am

"I need following format in sql"... okay so go ahead and do it. Please come back when you have a question.

2 solutions

Solution 1

Here is the sa(i)mple query to get the result:

SQL

Select SrNo, Month, Issues,
Sum(Issues) as SUM, 
(Issues * 100 / Sum(Issues)) as Percentage
From myTable

Posted 7-Jul-12 3:00am

Ganesan Senthilvel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Accepted Answer · 2012-07-07T04:58:00

Solution 2

You can use the SUM function[^] and the OVER clause[^].

SQL

SELECT SrNo, Month, Issues, SUM(Issues) OVER() AS SUM, CAST(Issues * (100.0 / SUM(Issues) OVER() AS DECIMAL (4, 2)) AS PERC
FROM myTable

The CAST function[^] is used to display the PERC with two digits.

Posted 7-Jul-12 4:58am

André Kraak

Updated 7-Jul-12 5:00am

v2

Comments

Kailash_Singh 8-Jul-12 23:58pm

Hi Krrak thanx a lot...Its worked
:) :)
Now I have another condition,
I have three field, SrNo(Int),Month(nvarchar(12)) and Issues(nvarchar(MAX)).

I use following querry:

Select SrNo,Month,count(Issues)as[Total]
from tblIssues
group by Month

But I need as following query

Select SrNo,Month,count(Issues),sum(count(Issues))
from tblIssues
group by Month

again thanx.....