June 25, 2010 at 12:59 am
Hi everyone...
I want to find the percentage of two column values with group by clause
i have two queries.
1.select zone, cast(count([incident id]) as float) from incident_detail group by zone
2.select zone, cast(count([incident id]) as float) from incident_detail where [slm status]='All Service Targets Breached' group by zone
i want to use second query as subquery in first one like this:
select zone, cast(count([incident id]) as float),(select zone, cast(count([incident id]) as float) from incident_detail where [slm status]='All Service Targets Breached' group by zone) from incident_detail group by zone
I want result like this:
ZoneTotal IncidentsIncidents with conditin
DAKC 37058 488
EAST 9281 143
NORTH 35535 620
SOUTH 38031 425
UNKNOWN 51
WEST 46244 359
[incident id], [slm status], zone are of type nvarchar
but it returns error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Can you please help me??
Your immediate action would be appreciated
June 25, 2010 at 4:17 am
You could use CTE's
Something like
;
WITH cte1 as
(
select zone, count([incident id]) as cnt1
from incident_detail
group by zone
), cte2 as
(
select zone, count([incident id]) as cnt2
from incident_detail
where [slm status]='All Service Targets
)
select cte1.zone, (cte2.cnt2 + 0.00) /cte1.cnt1
from cte1
inner join cte2 on cte1.zone=cte2.zone
If that's not what you're looking for please provide table definition (DDL script) ready to use sample data and expected result as described in the first link in my signature.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply