question regarding calculating percentage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

question regarding calculating percentage

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2009-06-26 : 17:29:53

I am trying to calculate %.

I have this query. I have a union of databases. Right now i am only showing two but there are several. The query is pretty long.

Right now it shows result like this

Name Disposition Test Test1 TotalCalls
jon save 1 1 2

Jack save 2 4 6

I want to show results like this

Name Disposition Test Test1 TotalCalls Percentofcalls
jon save 1 1 2 25%

Jack save 2 4 6 75%

TOTAL 8

declare @StartDate1 datetime
declare @EndDate1 datetime

set @StartDate1='6/15/2009'
set @enddate1='6/17/2009'

select [name],disposition,sum(TCDatabase1) as test, sum(TCDatabase2)AS Test1,
SUM(TCDatabase1+TCDatabase2)
as Total_Calls
from

(
-----------------------------------------------------------------------------------------------------------

SELECT LocalUserId as [name],disposition,
COUNT(*) AS TCDatabase1, 0 as TCDatabase2
FROM

test.dbo.tblCallReceived

WHERE

CallDate BETWEEN @StartDate1 AND @EndDate1
and disposition is null
and disposition ='Save' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
-----------------------------------------------------------------------------------------------------------

union all

-----------------------------------------------------------------------------------------------------------

SELECT LocalUserId as [name],disposition,
0 AS TCDatabase1,count(*) as TCDatabase2
FROM
test1.dbo.tblCallReceived
WHERE

CallDate BETWEEN @StartDate1 AND @EndDate1
and disposition ='Save' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
) AS rawdata

GROUP BY Disposition, name

ORDER BY name, Disposition

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-27 : 01:13:45

[code]declare @StartDate1 datetime
declare @EndDate1 datetime

set @StartDate1='6/15/2009'
set @enddate1='6/17/2009'

select [name],disposition,sum(TCDatabase1) as test, sum(TCDatabase2)AS Test1,
SUM(TCDatabase1+TCDatabase2)
as Total_Calls,SUM(TCDatabase1+TCDatabase2)*100.0/SUM(TCDatabase1+TCDatabase2) OVER () AS Percentofcalls
from

(
-----------------------------------------------------------------------------------------------------------

SELECT LocalUserId as [name],disposition,
COUNT(*) AS TCDatabase1, 0 as TCDatabase2
FROM

test.dbo.tblCallReceived

WHERE

CallDate BETWEEN @StartDate1 AND @EndDate1
and disposition is null
and disposition ='Save' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
-----------------------------------------------------------------------------------------------------------

union all

-----------------------------------------------------------------------------------------------------------

SELECT LocalUserId as [name],disposition,
0 AS TCDatabase1,count(*) as TCDatabase2
FROM
test1.dbo.tblCallReceived
WHERE

CallDate BETWEEN @StartDate1 AND @EndDate1
and disposition ='Save' AND len(callid) > 2
GROUP BY Disposition, LocalUserId
) AS rawdata

GROUP BY Disposition, name

ORDER BY name, Disposition

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources