How can I make multiple select statements on the same table?

Question

I have a table that stores some student work info. I need to select the hours based on the studentID and the quarter ID. Here is what I have:

SELECT
(SELECT hours FROM clinicalStudents WHERE quarterID='201101' and studentID='$studentID') as q1,             
(SELECT hours FROM clinicalStudents WHERE quarterID='201102' and studentID='$studentID') as q2,             
(SELECT hours FROM clinicalStudents WHERE quarterID='201103' and studentID='$studentID') as q3,             
(SELECT hours FROM clinicalStudents WHERE quarterID='201104' and studentID='$studentID') as q4

It's only giving me some numbers but not all of them. I ran this (minus the WHERE clause) in my server manager and received an error:

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

Any assistance would be great. Thanks!

EDIT:

The $studentID is generated in a while loop so I'm using the hours on that student before I move to the next one. I'm getting all the hours for one student in each quarter, adding them (this has to be done outside of the sql), storing results in a variable then moving to the next student. This works perfect when I get 1 quarter but i'm having an issue getting all the quarters.

EDIT Round 2: Did it in a rather lazy way I suppose:

I just selected all the hours and quarterID's for a specific student. Then ran a while(odbc_fetch_row()). If it was a 201101 I added it to the $q1 pile, 201102 added to the $q2 pile, and so on. Processing is a little slower but not a big issue with what I'm doing.

can you give an example of the data, what you expect to be returned and what is returned? — Robb, Dec 08 '10 at 20:23
on some students I get the hours on some students I don't. Basically, it's supposed to return the hours a student put in. Then I'm using the data that's returned. — dcp3450, Dec 08 '10 at 20:28

score 1 · Answer 1 · answered Dec 08 '10 at 20:23

1

trying use a SELECT TOP 1 or a LIMIT 1 in the query, depending which sql you are running.

EDIT Also, why are you trying to accomplish? This seems clunky and, depending your intended purpose, there is probably a better way available.

answered Dec 08 '10 at 20:23

Brad Christie

100,477
16
156
200

score 1 · Answer 2 · answered Dec 08 '10 at 20:29

1

I'm not sure what's your goal...

Maybe what you really want is:

select quarterID, sum(hours) 
from clinicalStudents 
where studentID='$studentID' 
group by 1

answered Dec 08 '10 at 20:29

TomaszK

435
2
4

score 0 · Answer 3 · answered Dec 08 '10 at 20:27

0

SELECT 
hours, SUBSTR(quarterId, 4, 2)
FROM 
clinicalStudents 
WHERE 
quarterID IN ('201101', '201102', '201103', '201104') and studentID='$studentID'

Depending on what database you are using, you will have to change the function SUBSTR

answered Dec 08 '10 at 20:27

Andrew White

1,770
4
25
43

Conrad Frix · Answer 4 · 2010-12-08T21:10:42.230

EDIT on loop.

Don't use a loop to do a SUM operation. Instead use the SUM aggregation operation

The allows you to project each quarter to each column.

   SELECT
 cs.studentid , q1.hours Q1, q2.hours Q2, q3.hours Q3, q4.hours Q4
    FROM 
    clinicalStudents cs
    (SELECT SUM(hours) hours , studentID 
     FROM clinicalStudents 
     WHERE quarterID='201101' and studentID='$studentID' 
     GROUP BY studentID ) q1
    LEFT join on cs.studentID = q1.clinicalStudents 
    (SELECT SUM(hours) hours , studentID  
     FROM clinicalStudents WHERE quarterID='201102' and studentID='$studentID' 
    GROUP BY studentID )  q2
    LEFT join on cs.studentID = q2.clinicalStudents              
    (SELECT SUM(hours) hours  , studentID 
     FROM clinicalStudents 
     WHERE quarterID='201103' and studentID='$studentID'
     GROUP BY studentID ) q3             
    LEFT join on cs.studentID = q3.clinicalStudents              
    (SELECT SUM(hours) hours , studentID 
     FROM clinicalStudents 
     WHERE quarterID='201104' and studentID='$studentID' GROUP BY studentID ) q4
    LEFT join on cs.studentID = q4.clinicalStudents          
    WHERE cs.studentID='$studentID'

i would LOVE to use SUM() but the way hours has been stored I can't. I've tried. Didn't work out. — dcp3450, Dec 08 '10 at 21:32

score 0 · Answer 5 · answered Dec 08 '10 at 22:07

Try this.

select studentID
      ,sum(case when quarterID = '201101' then hours end) as q1
      ,sum(case when quarterID = '201102' then hours end) as q2
      ,sum(case when quarterID = '201103' then hours end) as q3
      ,sum(case when quarterID = '201104' then hours end) as q4
  from clinicalStudents
 where quarterID in('201101', '201102', '201103', '201104')
 group by studentID;

How can I make multiple select statements on the same table?

5 Answers5

Linked