Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 join many select statement		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kazineel
Starting Member

16 Posts
Posted - 06/18/2010 :  09:41:44  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts
Posted - 06/18/2010 :  10:05:19  Show Profile  Visit webfred's Homepage  Reply with Quote
select ...
UNION ALL
select ...

and so on

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts
Posted - 06/18/2010 :  10:06:43  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 06/18/2010 :  10:11:33  Show Profile  Reply with Quote
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
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 06/18/2010 :  10:15:37  Show Profile  Reply with Quote
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
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 06/18/2010 :  10:18:18  Show Profile  Reply with Quote
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
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 06/18/2010 :  10:22:23  Show Profile  Reply with Quote
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
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 06/18/2010 :  10:35:31  Show Profile  Reply with Quote
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
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 06/18/2010 :  10:39:31  Show Profile  Reply with Quote
Remove Sub_category_id from the SELECT and GROUP BY
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 06/18/2010 :  10:52:13  Show Profile  Reply with Quote
quote:
Originally posted by vijayisonly

Remove Sub_category_id from the SELECT and GROUP BY



Thanx
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts
Posted - 06/18/2010 :  11:55:14  Show Profile  Reply with Quote
Np. You are welcome
Go to Top of Page

kazineel
Starting Member

16 Posts
Posted - 06/19/2011 :  11:46:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000