Sql help - 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

Sql help

Author

Topic

kazineel
Starting Member

16 Posts

Posted - 2011-06-20 : 08:10:09

Hello Guys,
Would you please help me about this sql? I have this sql and the result is
SELECT TestPlan_id
,status
, COUNT(TestCase_id) as Cnt
FROM TestExecution
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def')
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

The Result is like this

TestPlan_id status Cnt
1 N 2
1 P 18
1 P 17
1 P 14
1 Def 4
1 P 74
1 Def 4

But I want to display the result like this

TestPlan_id P N Def
1 2 1 4
2 5 5 6
Total 7 6 10

would you please help me?
THanks
Kazi

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-20 : 08:18:46

How do you get the result?
You have 7 rows for TestPlan_id 1. Can guess how you get the def entry but that's about it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kazineel
Starting Member

16 Posts

Posted - 2011-06-20 : 08:31:24

Is that possible to display Id 1 in on column then 2 another column like this way

quote:

TestPlan_id P N Def
1 2 1 4
2 5 5 6

Total 7 6 10

kazineel
Starting Member

16 Posts

Posted - 2011-06-20 : 12:25:55

Hello,
I have made my sql, now I can display records like this way
TestPlan_id P N Def
1 2 1 4
2 5 5 6

Here is my update sql

SELECT TestExecution.TmatsLog_id AS ID, TestPlan.testPlanName AS TestPlan,Lab.name AS Lab,User.loginName AS Tester,
sum(IF(TestExecution.status = 'P', 1, 0)) AS `P`,
sum(IF(TestExecution.status = 'U', 1, 0)) AS `U`,
sum(IF(TestExecution.status = 'Def', 1, 0)) AS `Def`,
sum(IF(TestExecution.status = 'B', 1, 0)) AS `B`,
sum(IF(TestExecution.status = 'X', 1, 0)) AS `X`,
sum(IF(TestExecution.status = 'F', 1, 0)) AS `F`,
sum(IF(TestExecution.status = 'N', 1, 0)) AS `N`

FROM TestExecution,TestPlan,Lab,User WHERE TestCycle_id=5 AND User.id=Tester_Id AND Lab.id=Lab_id AND TestPlan.id=TestPlan_id AND retest_flag = 0 GROUP BY status,TestPlan_id order by TestPlan_id

Now I want to calculate

sum(IF(TestExecution.status = 'P', 1, 0)) AS `P` + sum(IF(TestExecution.status = 'U', 1, 0)) AS `U`

can you tell me how to do this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources