June 26, 2009 at 3:09 pm
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
June 26, 2009 at 4:19 pm
this is on sql2005, right ?
Check the OVER clause in BOL. It may provide you a nice solution for your problem.
from BOL:
The following example shows using the OVER clause with an aggregate function in a calculated value.
Copy Code
USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
*100 AS DECIMAL(5,2))AS 'Percent by ProductID'
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
GO
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 26, 2009 at 4:46 pm
How will i use in my query. kInd of confused. it is sql server 2005
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply