Need help for querry - SQL Server Forums

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

Need help for querry

Author

Topic

RakeshSharma
Starting Member

2 Posts

Posted - 2009-11-23 : 03:17:54

Hi all,

I am storing my Automation results in sql server. In order to report testing summary i am displaying them to asp page.

Now i need to display percentage of pass/failed testcase against particullar functionality.
e.g. I have functionality named Entitymgmt. So i need to display(let us there were 10 testcase against entitymgmt out that 8 passed and 2 fialed)as follow:
Functinality Total Test Cases Passed Failed
Entitymgmt 10 8 2

I am writing following query:
"Select Functinality, count(TestScript) as [Total Test Cases], Status, count(Status) as [pass/fail count] from Results group by TestScript, status"

and it is returning like as follow:
Functionality Total test cases status pass/failed count
Entitymgmt 8 passed 8
Entitymgmt 2 fialed 2

I need to write subquery for Status column so that it returns me twi column named passed and failed count as below format:

Functinality Total Test Cases Passed Failed
Entitymgmt 10 8 2

Please help
Thanks in advance.

Regards,
~rakesh
"Pay no attention to appearing. Being is alone important.”

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 09:12:26

[code]Select Functinality,
count(TestScript) as [Total Test Cases],
count(case when Status='Passed' then 1 else null end) as [pass count] ,
count(case when Status='failed' then 1 else null end) as [fail count]
from Results
group by TestScript
[/code]

RakeshSharma
Starting Member

2 Posts

Posted - 2009-11-24 : 00:37:37

Thanks Visakh!!

It is working according to my requirment

.

Regards,
~rakesh
"Pay no attention to appearing. Being is alone important.”

april198474
Starting Member

11 Posts

Posted - 2009-11-24 : 06:43:25

You can use the following query:

declare @total int, @passed int, @failed int

select @total = (select count(TestScript) from Results)

select @passed = (select count(TestScript) from Results where status = 'passed')

select @failed = (select count(TestScript) from Results where status = 'failed')

select distinct Functinality, @total as [Total Test Cases], @passed as [Passed], @failed as [Failed]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-27 : 02:07:34

quote:
Originally posted by april198474

You can use the following query:

declare @total int, @passed int, @failed int

select @total = (select count(TestScript) from Results)

select @passed = (select count(TestScript) from Results where status = 'passed')

select @failed = (select count(TestScript) from Results where status = 'failed')

select distinct Functinality, @total as [Total Test Cases], @passed as [Passed], @failed as [Failed]

______________________
April
http://www.comm100.com/livechat/

this will give same count values irrespective of functionality field as you're not grouping on anything. also no need to store values in variables once you apply group by

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources