Percentage of two column values with grouping

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

General SQL Server Forums

New to SQL Server Programming

Percentage of two column values with grouping

Author

Topic

bhavehpatel2004
Starting Member

2 Posts

Posted - 2010-06-25 : 03:07:43

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:

Zone Total Incidents Incidents with conditin
DAKC 37058 488
EAST 9281 143
NORTH 35535 620
SOUTH 38031 425
UNKNOWN 51 0
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

Regards,
Bhavesh Patel.

dattatreysindol
Starting Member

20 Posts

Posted - 2010-06-25 : 05:01:28

Hi There -

Try to use something like this.

SELECT
WithoutCondition.Zone
, WithoutCondition.Count AS [Total Incidents]
, WithCondition.Count AS [Incidents with conditin]
FROM
(select zone, cast(count([incident id]) as float) AS Count from incident_detail group by zone) WithoutCondition
INNER JOIN (select zone, cast(count([incident id]) as float) from incident_detail where [slm status]='All Service Targets Breached' group by zone) WithCondition
ON WithoutCondition.Zone = WithCondition.Zone

This is just a pseudo code. If you try it on these lines you should be able to get it.
Other option is to use Common Table Expressions (CTEs).

Hope this helps!

Dattatrey Sindol
http://mytechnobook.blogspot.com/

This information is provided "AS IS" with no warranties, and confers no rights.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-25 : 05:06:53

[code]
select zone,
count([incident id]) as [Total Incidents],
count(case when [slm status] = 'All Service Targets Breached' then [incident id] end) as [Incidents with conditin]
from incident_detail
group by zone
[/code]

why are you casting the count to float ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-25 : 05:11:10

And if you want the percentage of with "Incidents with conditin" over total


select zone, 
       [Total Incidents],
       [Incidents with conditin],
       [Incidents with conditin] * 100.0 / [Incidents with conditin] as [Percentage]
from
(
select zone, 
       count([incident id]) as [Total Incidents],
       count(case when [slm status] = 'All Service Targets Breached' then [incident id] end) as [Incidents with conditin]
from   incident_detail 
group by zone
) a

KH
[spoiler]Time is always against us[/spoiler]

bhavehpatel2004
Starting Member

2 Posts

Posted - 2010-06-25 : 05:32:09

quote:

Originally posted by khtan

And if you want the percentage of with "Incidents with conditin" over total


select zone, 
       [Total Incidents],
       [Incidents with conditin],
       [Incidents with conditin] * 100.0 / [Incidents with conditin] as [Percentage]
from
(
select zone, 
       count([incident id]) as [Total Incidents],
       count(case when [slm status] = 'All Service Targets Breached' then [incident id] end) as [Incidents with conditin]
from   incident_detail 
group by zone
) a

KH
[spoiler]Time is always against us[/spoiler]

Thank you very much Khtan.... Its working perfectly....
Again thank u very much.... u rocks...

Regards,
Bhavesh Patel.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-25 : 05:36:38

you are welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources