Click here to Skip to main content
12,242,492 members (47,996 online)
Rate this:
 
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 3:53am
Edited 8-Sep-11 7: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 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: 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 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
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"."
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
Top Experts
Last 24hrsThis month


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100