15,040,212 members
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
[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.

## 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

## 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.
v2
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.....