Click here to Skip to main content
15,040,212 members
Please Sign up or sign in to vote.
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
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.

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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900