Friday, March 23, 2012

Merge 2 queries

Hi all i have 2 queries. One of which works out where 2 values are equal and the other where where all value are there.. This is in order for me to divid one by another to get percentage.

THIS IS FOR ALL
select results.playerid, players.playername, count (results.playerid) as allgames
from results, players
where results.playerid = players.playerid
group by results.playerid, players.playername
order by allgames desc

THIS IS FOR WON
select results.playerid, players.playername, count (results.result) as wongames
from results, players
where results.result = 1 and
results.playerid = players.playerid
group by results.result, results.playerid, players.playername
order by results.playerid asc

I can make them have the same view output...

How can i merge them into one to get percentage.. Ideally i would like to this on the fly because once i have all the result i want i want to do a front end on the net.

I am using SQL server Enterprise Manage.

Thanks in advance.declare @.var1 float, @.var2 float, @.var3 float

set @.var1 = (select count (results.playerid) from results, players where results.playerid = players.playerid)
set @.var2 = (select count (results.result) as wongames from results, players where results.result = 1 and results.playerid = players.playerid)

set @.var3 = @.var2/@.var1 * 100

select @.var3
--or--
select convert(varchar(50),@.var3)+'%'|||thank you!! i'll try it tomorrow.|||is this method only able to send single value???

"Subquery returned more than 1 value"

No comments:

Post a Comment