Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
             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
Edited 8-Sep-11 6:56am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

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,
       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.
  Permalink  
Comments
SandraCy at 8-Sep-11 13:50pm
   
:-) :-) :-) thanks thanks. Just what i needed. God bless you!
Mika Wendelius at 8-Sep-11 13:53pm
   
You're welcome :)
SandraCy at 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 at 7-Oct-11 15:41pm
   
Sandra Boitumelo Sefara.
Rate this: bad
good
Please Sign up or sign in to vote.

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'
and ProgressionStatus='P' group by Grade
 
Select COUNT(ProgressionStatus) from LearnerProgression where AcademicYear='2010' and EmisCode='500171421'
group by Grade
  Permalink  
Comments
MaulikDusara at 8-Sep-11 9:07am
   
group by clause will always return more than one value
SandraCy at 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 at 8-Sep-11 9:28am
   
for that you need to add group in where clause
Om Prakash Pant at 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 at 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 at 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"."
Rate this: bad
good
Please Sign up or sign in to vote.

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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,838
1 Tadit Dash 310
2 OriginalGriff 283
3 Sergey Alexandrovich Kryukov 260
4 Peter Leow 220
0 Sergey Alexandrovich Kryukov 9,440
1 OriginalGriff 5,618
2 Peter Leow 4,280
3 Maciej Los 3,540
4 Abhinav S 3,363


Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 8 Sep 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid