Using temp tables, syntax error 1

[AgentM]

Hi,
I am new to TSQL and have a problem with temp tables

CREATE TABLE #TCOUNT( PASSED INT, FAILED INT )

INSERT INTO #TCOUNT(PASSED,FAILED)
VALUES (SELECT count(ID) FROM  ExamResults er
	WHERE  (er.[lastresult] = @mark1) 
	AND ( er.[curresult] = @mark3 or er.curresult= @mark4 or er.[curresult] = @mark5)
, 1)

I get the following error "Subqueries are not allowed in this context. Only scalar expressions are allowed."
the variables are all defined and declared.
Any help appreciated.

 

[gmmastros]

CREATE TABLE #TCOUNT( PASSED INT, FAILED INT )

INSERT INTO #TCOUNT(PASSED,FAILED)
SELECT count(ID), 1
FROM   ExamResults er
WHERE  (er.[lastresult] = @mark1)
       AND ( er.[curresult] = @mark3 or er.curresult= @mark4 or er.[curresult] = @mark5)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AgentM]

thanks,
Brings me to another question,
I want to display count of people passed and failed as two columns in a single table.

Can you help?

 

[gmmastros]

I don't think you've provided enough information for me to help you with the query. but, consider this.

A count is the same as summing values of 1. so....

Select Count(*) As AllRecords,
       Sum(Case When Grade In ('A','B','C','D') Then 1 Else 0 End) As CountOfPassed,
       Sum(Case When Grade = 'F' Then 1 Else 0 End) As CountOfFailed
From   SomeTable

Obviously, you won't be able to run that query, but it's more important for you to understand what it's doing. If the grade is A, B, C, or D, the case expression returns a 1. Summing the 1's is essentially the same as counting the 'passes'.

Make sense?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom