Select Statement Question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select Statement Question

Author

Topic

Scottt40
Starting Member

5 Posts

Posted - 2006-08-17 : 15:22:15

I want to be able to use the following query but have the Count(TotalPassed) on count when their is a specific totalPassed of 4

SELECT COUNT(AssessID) AS NumberTest, COUNT(PushUpsPassed) AS PUPassed, COUNT(CurlUpsPassed) AS CUPassed, COUNT(MilePassed)
AS MilePassed, COUNT(SitReachPassed) AS SRPassed, Grade, Gender, COUNT(TotalPassed) AS TotalPassed4
FROM AssessTbl
WHERE (SchoolID = @SchoolID) AND (TestDate = @TestDate) AND (MedEx = @MedEx) AND (Grade = @Grade)
GROUP BY Grade, Gender

I have tried all kinds of things but they just return less information by the other count statements.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-17 : 15:41:41

I think this is what you want:

SELECT	COUNT(AssessID) AS NumberTest,
	COUNT(PushUpsPassed) AS PUPassed,
	COUNT(CurlUpsPassed) AS CUPassed,
	COUNT(MilePassed) AS MilePassed,
	COUNT(SitReachPassed) AS SRPassed,
	Grade,
	Gender,
	COUNT(TotalPassed) AS TotalPassed4
FROM	AssessTbl
WHERE	SchoolID = @SchoolID
	AND TestDate = @TestDate
	AND MedEx = @MedEx
	AND Grade = @Grade
GROUP BY Grade,
	Gender
HAVING	COUNT(TotalPassed) = 4

Scottt40
Starting Member

5 Posts

Posted - 2006-08-17 : 15:53:38

quote:

Originally posted by blindman

I think this is what you want:

SELECT	COUNT(AssessID) AS NumberTest,
	COUNT(PushUpsPassed) AS PUPassed,
	COUNT(CurlUpsPassed) AS CUPassed,
	COUNT(MilePassed) AS MilePassed,
	COUNT(SitReachPassed) AS SRPassed,
	Grade,
	Gender,
	COUNT(TotalPassed) AS TotalPassed4
FROM	AssessTbl
WHERE	SchoolID = @SchoolID
	AND TestDate = @TestDate
	AND MedEx = @MedEx
	AND Grade = @Grade
GROUP BY Grade,
	Gender
HAVING	COUNT(TotalPassed) = 4

I really appreciate you help but, the querry gave me nothing. I only want the Count(TotalPassed) to be gotten when the value of TotalPassed = 4 and I do not want it to affect the rest of the columns. I have tried using to statements using the UNION and UNIONBY statements but causes error. I appreciate all the help. Thanks.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-17 : 17:01:12

Try again:

SELECT	COUNT(AssessID) AS NumberTest,
	COUNT(PushUpsPassed) AS PUPassed,
	COUNT(CurlUpsPassed) AS CUPassed,
	COUNT(MilePassed) AS MilePassed,
	COUNT(SitReachPassed) AS SRPassed,
	Grade,
	Gender,
	sum(case TotalPassed when 4 then 1 else 0 end) AS TotalPassed4
FROM	AssessTbl
WHERE	SchoolID = @SchoolID
	AND TestDate = @TestDate
	AND MedEx = @MedEx
	AND Grade = @Grade
GROUP BY Grade,
	Gender

Scottt40
Starting Member

5 Posts

Posted - 2006-08-17 : 23:20:08

Thanks a bunch, it worked as stated in the sql statement, but the logic I was looking for was wrong. Now I need to qualify each statement for MilePassed, PushUpsPassed, CurlUpsPassed, SitReachPassed so it only counts them when they are true. Would I use a similar statement to the TotalPassed like Sum(Case MilePassed When True to 1 and False to 0)? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 03:09:44

Of course. You can even include an IN too, or a ">".

SELECT		COUNT(AssessID) AS NumberTest,
		sum(case when PushUpsPassed in (1, 3, 5) then 1 else 0 end) AS PUPassed,
		sum(case when CurlUpsPassed > 99 then 1 else 0 end) AS CUPassed,
		sum(case MilePassed when 243342 then 1 else 0 end) AS MilePassed,
		sum(case SitReachPassed when -2 then 1 else 0 end) AS SRPassed,
		Grade,
		Gender,
		sum(case TotalPassed when 4 then 1 else 0 end) AS TotalPassed4
FROM		AssessTbl
WHERE		SchoolID = @SchoolID
		AND TestDate = @TestDate
		AND MedEx = @MedEx
		AND Grade = @Grade
GROUP BY	Grade,
		Gender

Peter Larsson
Helsingborg, Sweden

Scottt40
Starting Member

5 Posts

Posted - 2006-08-18 : 08:27:20

I have already calculated whether they have passed the assessment or not in the program, and store it in the database as a boolean. So I want to only return those that have a value of true.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-18 : 08:55:29

[code]SELECT COUNT(AssessID) AS NumberTest,
sum(case when PushUpsPassed = 1 then 1 else 0 end) AS PUPassed,
sum(case when CurlUpsPassed = 1 then 1 else 0 end) AS CUPassed,
sum(case when MilePassed = 1 then 1 else 0 end) AS MilePassed,
sum(case when SitReachPassed =1 then 1 else 0 end) AS SRPassed,
Grade,
Gender,
sum(case TotalPassed when 4 then 1 else 0 end) AS TotalPassed4
FROM AssessTbl
WHERE SchoolID = @SchoolID
AND TestDate = @TestDate
AND MedEx = @MedEx
AND Grade = @Grade
GROUP BY Grade,
Gender[/code]
Peter Larsson
Helsingborg, Sweden

Scottt40
Starting Member

5 Posts

Posted - 2006-08-18 : 15:45:59

Gives me an error in SQL Server 2005

Invalid column name 'true'
for all columns

Could true be a boolean like true = 1 and false =0

so I tried with no luck (case milepassed when 1 then 1 else 0)

HELP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources