Return one table instead of 3?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Return one table instead of 3??

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-16 : 14:19:57

Hello!

How does one make this statement return one table instead of three??

SELECT (Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='P' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as P
select (Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='W' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as W
select (Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='F' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as F

--PhB

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-16 : 14:22:28

SELECT COUNT(*) ...
UNION ALL
SELECT COUNT(*) ...
UNION ALL
SELECT COUNT(*) ...

Tara Kizer
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 14:22:52

Use UNION ALL.

Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='P' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic'

UNION ALL

Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='W' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic'

UNION ALL

Select Count(*) from qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID where qaTests.TestResult='F' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic'

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 14:22:55

You mean something like this:

SELECT (Select Count(*) from qaTestSuite with(NOLOCK)
INNER JOIN qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
where qaTests.TestResult='P' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as P
UNION ALL
select (Select Count(*) from qaTestSuite with(NOLOCK)
INNER JOIN qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
where qaTests.TestResult='W' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as W
UNION ALL
select (Select Count(*) from qaTestSuite with(NOLOCK)
INNER JOIN qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
where qaTests.TestResult='F' and qaTestSuite.TestMode='r' and TestIDInternal='emailbasic') as F

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-16 : 14:25:29

Holy cow!!

we are like sharks waiting to attack on an unread post...

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 14:26:35

Or you can do this, which is much more efficient!

SELECT		COUNT(*)
FROM		qaTestSuite WITH (NOLOCK)
INNER JOIN	qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE		qaTestSuite.TestMode = 'r'
		AND TestIDInternal = 'emailbasic'
		AND qaTests.TestResult IN ('p', 'w', 'f')
GROUP BY	qaTests.TestResult

Peter Larsson
Helsingborg, Sweden

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-16 : 17:45:14

Have I told you all how beautiful you are?

Thanks so much!!!

--PhB

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-17 : 08:45:53

Just tried these queries, and what they give me is one column, I need each (P,W,F) to be it's own field. Is there a way to do this?

Thanks!

--PhB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:49:10

[code]SELECT SUM(CASE WHEN qaTests.TestResult = 'p' THEN 1 ELSE 0 END) AS [p],
SUM(CASE WHEN qaTests.TestResult = 'w' THEN 1 ELSE 0 END) AS [w],
SUM(CASE WHEN qaTests.TestResult = 'f' THEN 1 ELSE 0 END) AS [f]
FROM qaTestSuite WITH (NOLOCK)
INNER JOIN qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE qaTestSuite.TestMode = 'r'
AND TestIDInternal = 'emailbasic'
AND qaTests.TestResult IN ('p', 'w', 'f')
~~GROUP BY qaTests.TestResult~~[/code]
Peter Larsson
Helsingborg, Sweden

EDIT: Bug spotted by Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 08:51:12

[code]SELECT qaTests.TestResult,
COUNT(*) AS Total
FROM qaTestSuite WITH (NOLOCK)
INNER JOIN qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE qaTestSuite.TestMode = 'r'
AND TestIDInternal = 'emailbasic'
AND qaTests.TestResult IN ('p', 'w', 'f')
GROUP BY qaTests.TestResult[/code]

Peter Larsson
Helsingborg, Sweden

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-17 : 09:01:01

I appreciate your help very much. But what that just gives me the same thing with one more field.
Like this:
Res Count
P 1
W 2
F 3

What I need is this:
P W F
1 2 3

Thanks so much!

--PhB

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 09:12:47

Have you tried both of Peter's queries? Query 1 should give you desired output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 09:25:54

Why is this?
I don't even remember the number of times OP disregards perfectly valid suggestions. Is it me?
Or OP's suffer from stress?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 09:29:38

Peter,
I think you should slow down a bit. You post so many solutions in so less time, that OP never gets time to check all the solutions. So they just go for the last one!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 09:36:08

It's stress then?

Peter Larsson
Helsingborg, Sweden

phrankbooth
Posting Yak Master

162 Posts

Posted - 2007-04-17 : 09:41:38

Hhhmmmm, sorry, for some reason I did not see your other answer, I see it now. Yes, that it!!

Thanks very much!!

--PhB

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-17 : 09:43:41

quote:

Originally posted by Peso

SELECT		SUM(CASE WHEN qaTests.TestResult = 'p' THEN 1 ELSE 0 END) AS [p],
		SUM(CASE WHEN qaTests.TestResult = 'w' THEN 1 ELSE 0 END) AS [w],
		SUM(CASE WHEN qaTests.TestResult = 'f' THEN 1 ELSE 0 END) AS [f]
FROM		qaTestSuite WITH (NOLOCK)
INNER JOIN	qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE		qaTestSuite.TestMode = 'r'
		AND TestIDInternal = 'emailbasic'
		AND qaTests.TestResult IN ('p', 'w', 'f')
GROUP BY	qaTests.TestResult

Peter Larsson
Helsingborg, Sweden

You should not be grouping on TestResult if he wants everything on 1 row.

SELECT		SUM(CASE WHEN qaTests.TestResult = 'p' THEN 1 ELSE 0 END) AS [p],
		SUM(CASE WHEN qaTests.TestResult = 'w' THEN 1 ELSE 0 END) AS [w],
		SUM(CASE WHEN qaTests.TestResult = 'f' THEN 1 ELSE 0 END) AS [f]
FROM		qaTestSuite WITH (NOLOCK)
INNER JOIN	qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE		qaTestSuite.TestMode = 'r'
		AND TestIDInternal = 'emailbasic'
		AND qaTests.TestResult IN ('p', 'w', 'f')
GROUP BY	qaTests.TestResult

- Jeff
http://weblogs.sqlteam.com/JeffS

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 09:45:59

quote:

Originally posted by jsmith8858

quote:

Originally posted by Peso

SELECT		SUM(CASE WHEN qaTests.TestResult = 'p' THEN 1 ELSE 0 END) AS [p],
		SUM(CASE WHEN qaTests.TestResult = 'w' THEN 1 ELSE 0 END) AS [w],
		SUM(CASE WHEN qaTests.TestResult = 'f' THEN 1 ELSE 0 END) AS [f]
FROM		qaTestSuite WITH (NOLOCK)
INNER JOIN	qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE		qaTestSuite.TestMode = 'r'
		AND TestIDInternal = 'emailbasic'
		AND qaTests.TestResult IN ('p', 'w', 'f')
GROUP BY	qaTests.TestResult

Peter Larsson
Helsingborg, Sweden

You should not be grouping on TestResult if he wants everything on 1 row.

SELECT		SUM(CASE WHEN qaTests.TestResult = 'p' THEN 1 ELSE 0 END) AS [p],
		SUM(CASE WHEN qaTests.TestResult = 'w' THEN 1 ELSE 0 END) AS [w],
		SUM(CASE WHEN qaTests.TestResult = 'f' THEN 1 ELSE 0 END) AS [f]
FROM		qaTestSuite WITH (NOLOCK)
INNER JOIN	qaTests WITH (NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID
WHERE		qaTestSuite.TestMode = 'r'
		AND TestIDInternal = 'emailbasic'
		AND qaTests.TestResult IN ('p', 'w', 'f')
GROUP BY	qaTests.TestResult

- Jeff
http://weblogs.sqlteam.com/JeffS

~~I think GROUP BY is needed since field - "TestResult" is used inside SUM().~~

EDIT: I take my words back. No need for GROUP BY.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-17 : 09:46:35

quote:
Originally posted by Peso

It's stress then?

Peter Larsson
Helsingborg, Sweden

No! OP can't cope up with your speed

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 09:48:21

Thanks Jeff.
The power of Copy & Paste made me not see that.

Peter Larsson
Helsingborg, Sweden

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-04-17 : 11:09:14

Where is our "Do your formatting in the front end"?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources