| Author |
 Topic  |
|
|
kazineel
Starting Member
16 Posts |
 Posted - 06/18/2010 : 09:41:44
|
Hey Guys, I want to join my all select statement, here is my all select statement. I don't understand how to join,
$sql="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as TTC FROM TestExecution WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result=mysql_query($sql);
$sql1="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result1=mysql_query($sql1);
$sql2="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result2=mysql_query($sql2);
$sql3="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result3=mysql_query($sql3);
$sql4="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result4=mysql_query($sql4);
$sql5="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result5=mysql_query($sql5);
$sql6="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result6=mysql_query($sql6);
$sql7="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result7=mysql_query($sql7);
|
|
|
vijayisonly
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 06/18/2010 : 10:04:16
|
I think you just need this
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count]
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 |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 06/18/2010 : 10:05:19
|
select ...
UNION ALL
select ...
and so on
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 06/18/2010 : 10:06:43
|
quote:
Originally posted by vijayisonly
I think you just need this
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count]
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
Vijay don't post here!
USA is playing NOW 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
vijayisonly
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 06/18/2010 : 10:11:33
|
quote:
Originally posted by webfred
quote:
Originally posted by vijayisonly
I think you just need this
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count]
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
Vijay don't post here!
USA is playing NOW
No, you're never too old to Yak'n'Roll if you're too young to die.
Headed to work after watching most of Germany - Serbia. You shud be sulking with beer...what are you doing posting here?? 
Boy...dint see that coming  |
|
|
|
kazineel
Starting Member
16 Posts |
Posted - 06/18/2010 : 10:15:37
|
quote:
Originally posted by vijayisonly
I think you just need this
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count]
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
I got this error
SQL query: Documentation
SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id,
STATUS , COUNT( TestCase_id ) AS [Count]
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
LIMIT 0 , 30
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Count]
FROM TestExecution
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' ' at line 6
|
|
|
|
vijayisonly
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 06/18/2010 : 10:18:18
|
You seem to be using MySQL.This is a dedicated MS SQL Server Forum.
Try it without []
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_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
|
|
|
|
kazineel
Starting Member
16 Posts |
Posted - 06/18/2010 : 10:22:23
|
quote:
Originally posted by vijayisonly
You seem to be using MySQL.This is a dedicated MS SQL Server Forum.
Try it without []
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_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
BINGO!!!! working. THanks Baddy |
|
|
|
kazineel
Starting Member
16 Posts |
Posted - 06/18/2010 : 10:35:31
|
quote:
Originally posted by vijayisonly
You seem to be using MySQL.This is a dedicated MS SQL Server Forum.
Try it without []
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_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
One question, Can I count the result by status (P,N,...) , bcz if you see my result then you understand
Full Texts
TestPlan_id Category_id Sub_category_id Tester_Id status Cnt
1 1 1 22 N 2
1 1 1 22 P 18
1 1 2 22 P 17
1 1 3 22 P 14
1 1 4 22 Def 4
1 1 6 22 P 74
1 1 7 22 Def 4 |
|
|
|
vijayisonly
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 06/18/2010 : 10:39:31
|
Remove Sub_category_id from the SELECT and GROUP BY
|
|
|
|
kazineel
Starting Member
16 Posts |
Posted - 06/18/2010 : 10:52:13
|
quote:
Originally posted by vijayisonly
Remove Sub_category_id from the SELECT and GROUP BY
Thanx |
|
|
|
vijayisonly
Flowing Fount of Yak Knowledge
USA
1836 Posts |
Posted - 06/18/2010 : 11:55:14
|
| Np. You are welcome |
|
|
|
kazineel
Starting Member
16 Posts |
Posted - 06/19/2011 : 11:46:12
|
quote:
Originally posted by vijayisonly
Np. You are welcome
Hey Guys,
About this query,
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
How to display the result row to column
like this way
TestPlan_id P N Def
1 2 1 4
2 5 5 6
Total 7 6 10
|
|
|
| |
Topic |
|
|
|
join many select statement