12,825,743 members (38,286 online)
Rate this:
See more:
i've got this code which must count the total number of learners that passed, count overall total number of learners then finally Number_Passed/Total_Number*100

```Declare @TotPass int,@TotNum int, @PasRate Decimal(3,0),
@intFlag INT = 7
WHILE (@intFlag >0 )
BEGIN
set @TotNum=(Select COUNT(ProgressionStatus)
from LearnerProgression

)

set @TotPass =(Select COUNT(ProgressionStatus)
from LearnerProgression
and ProgressionStatus='P'
)
set @PasRate= (convert(decimal,@TotPass)/CONVERT(decimal,@TotNum)*100 )
select @TotPass,@TotPass,@PasRate as Rate
set @intFlag=@intFlag-1
END```

i get this error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

i need a code to create an array.
Posted 8-Sep-11 3:53am
Updated 8-Sep-11 7:56am
Wendelius343.8K
v3

Rate this:

## Solution 3

First of all, there's actually no such concept in SQL language as an array. SQL is a set based language.

@TotNum and @TotPass are variables so they can hold just a single value at a time. This is the reason you get the error.

If I understood correctly, you want:
- total number of passed students
- total number of students
- pass ratio
And all this has to be calculated per Grade. And in the end you want to return the results. If this was correct you could do this with a single statement. Something like:
```SELECT Grades.Grade,
FROM LearnerProgression lp
COUNT(*) AS NumberOfPassed
FROM LearnerProgression lp
AND   lp.EmisCode     = '500171421'
COUNT(*) AS TotalNumber
FROM LearnerProgression lp
AND   lp.EmisCode     = '500171421'
```

This requires a bit explanation: The query contains 3 inline views, basically select statements that are defined in FROM clause. All of them return number of rows:
- first one (grades) gets all the grades that exist, each only once (DISTINCT)
- the second one gets the number of passed learners per grade
- and the third one gets the total number of learners per grade

In the outer WHERE clause rows from these views are joined using the grade and the select portion simply defines the columns to return and the calculation for the pass ratio.
SandraCy 8-Sep-11 13:50pm

:-) :-) :-) thanks thanks. Just what i needed. God bless you!
Mika Wendelius 8-Sep-11 13:53pm

You're welcome :)
SandraCy 7-Oct-11 15:40pm

Mika, im an IT student from South Africa, and im so inlove with DBMS...! please invite me on facebook if u don't mind.
SandraCy 7-Oct-11 15:41pm

Sandra Boitumelo Sefara.
Rate this:

## Solution 1

the most likely cause is the query which is returning more than one result as it appears from the error message.

please check that the queries return only one value.
```Select COUNT(ProgressionStatus) from LearnerProgression where AcademicYear='2010' and EmisCode='500171421'

Select COUNT(ProgressionStatus) from LearnerProgression where AcademicYear='2010' and EmisCode='500171421'
```
MaulikDusara 8-Sep-11 9:07am

group by clause will always return more than one value
SandraCy 8-Sep-11 9:14am

if i remove the group by clause, it gives me the pass rate for the entire school and i want it group by grades.:) Thats y i need an array to store all grades
MaulikDusara 8-Sep-11 9:28am

for that you need to add group in where clause
Om Prakash Pant 8-Sep-11 9:32am

Not necessarily group by will always return more than one value. it depends.
Instead of array, create a temp table and store the result and do the processing..
Om Prakash Pant 8-Sep-11 9:38am

Create table #test_table (grade varchar(10), tot_pass int, tot_num int,PasRate Decimal(3,0))

Declare @TotPass int,@TotNum int, @PasRate Decimal(3,0),@intFlag INT = 7

insert into #test_table

--update total pass column and total column using the "update" statement..
SandraCy 8-Sep-11 10:18am

just heard that sql does not support arrays. and i got this code which works like an array.

Create TYPE PassType as table (Grade int,TotoP int)
Declare @Pass PassType
from LearnerProgression

But it gives me this error. "Msg 2715, Level 16, State 3, Line 6
Column, parameter, or variable #1: Cannot find data type PassType.
Parameter or variable '@Pass' has an invalid data type.
Msg 1087, Level 16, State 1, Line 3
Must declare the table variable "@Pass"."
Rate this:

## Solution 2

Hi SandraCy,
your query is correct just do one change that remove group by clause from your query and you will get the result.

Top Experts
Last 24hrsThis month
 OriginalGriff 310 ppolymorphe 155 Jochen Arndt 98 F-ES Sitecore 75 Karthik Bangalore 70
 OriginalGriff 5,367 Graeme_Grant 3,870 Karthik Bangalore 3,681 ppolymorphe 2,739 Jochen Arndt 2,666