how to create an array in sql server 2008

Question

0.00/5 (No votes)

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

SQL

Declare @TotPass int,@TotNum int, @PasRate Decimal(3,0),
        @intFlag INT = 7
WHILE (@intFlag >0 )
BEGIN
set @TotNum=(Select COUNT(ProgressionStatus)
             from LearnerProgression
             where AcademicYear='2010' and EmisCode='500171421'
             group by Grade

             )

set @TotPass =(Select COUNT(ProgressionStatus)
              from LearnerProgression
              where AcademicYear='2010' and EmisCode='500171421'
              and ProgressionStatus='P'
              group by Grade
             )
 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 2:53am

SandraCy

Updated 8-Sep-11 6:56am

Wendelius

v3

Add a Solution

3 solutions

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.

SQL

Select COUNT(ProgressionStatus) from LearnerProgression where AcademicYear='2010' and EmisCode='500171421'
and ProgressionStatus='P' group by Grade

Select COUNT(ProgressionStatus) from LearnerProgression where AcademicYear='2010' and EmisCode='500171421'
group by Grade

Posted 8-Sep-11 2:56am

Om Prakash Pant

Comments

[no name] 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

[no name] 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
SELECT grade, COUNT(ProgressionStatus),0,0 from LearnerProgression
where AcademicYear='2010' and EmisCode='500171421'
group by Grade

--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
insert @Pass Select Grade, COUNT(ProgressionStatus)
from LearnerProgression
where AcademicYear='2010' and EmisCode='500171421'
group by Grade

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"."

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.

Posted 8-Sep-11 3:07am

MaulikDusara

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-09-08T07:23:00

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:

SQL

SELECT Grades.Grade,
       GradesPassed.NumberOfPassed,
       GradesTotal.TotalNumber,
       GradesPassed.NumberOfPassed / GradesTotal.TotalNumber * 100 AS PassRatio
FROM (SELECT DISTINCT Grade
      FROM LearnerProgression lp
      WHERE lp.AcademicYear = '2010' 
      AND   lp.EmisCode     = '500171421') Grades,
     (SELECT lp.Grade,
             COUNT(*) AS NumberOfPassed
      FROM LearnerProgression lp
      WHERE lp.AcademicYear = '2010' 
      AND   lp.EmisCode     = '500171421'
      GROUP BY lp.Grade) GradesPassed,
     (SELECT lp.Grade,
             COUNT(*) AS TotalNumber
      FROM LearnerProgression lp
      WHERE lp.AcademicYear = '2010' 
      AND   lp.EmisCode     = '500171421'
      GROUP BY lp.Grade) GradesTotal
WHERE GradesPassed.Grade = Grades.Grade
AND   GradesTotal.Grade  = Grades.Grade

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.