I'm trying to count the number of 'classes' each student is taking, but I'm supposed to report back 0 if there are none. Right now, it just doesn't respond with the student.
Anyone out there been through a database class who solved this?
Name:
Anonymous2008-10-29 19:57
Drill down and chop that shit up bro.
Name:
Anonymous2008-10-29 19:58
.. UNION SELECT student_id, 0 AS n_classes FROM students
Name:
Anonymous2008-10-29 20:13
Uhm:
select count(sid)
from studentclasses
where sid = 123456;
That returns 0 if student id 123456 doesn't exist. What's the problem?
Name:
How to count 02008-10-29 20:18
select student.student_id, student_name, count(student.student_id)
from student, enrolls
where student.student_id=enrolls.student_id
group by student.student_id, student_name;
This is what I'm currently using.
Are you suggesting I union after this whole statement like:
.
.
.
union
select student.student_id, student_name, 0 as n_classes /*new variable name?*/
from student
Name:
Anonymous2008-10-29 20:23
>>5
Lol, you don't need to go through all of that hassle just to select 0. This reminds me of those obfuscated programming contests, where they write those large programs just to do "hello world".
Name:
How to count 02008-10-29 20:37
No, I want it to print out a table.
STUDENT_ID STUDENT_NAME COUNT(STUDENT.STUDENT_ID)
---------- -------------------- -------------------------
1004 Ivy Chang 2
1006 Tom Taylor 2
1003 John Smith 6
1002 David Baker 1
1001 James Walter 4
1005 James Fowler 1
Like this, except there's a STUDENT_ID 1007 named Tom Taylor
(teacher thinks she's being tricky)
Who has signed up for no classes.
Blasted thing is, even counting the null comes up with 1.
Could I use something else to have it simply count 0 for him, despite counting starting at 1?
Name:
Anonymous2008-10-29 20:50
COUNT(STUDENT.STUDENT_ID) there's a STUDENT_ID 1007 counting the null comes up with 1
1007 is not null
Name:
How to count 02008-10-29 22:11
We're so lazy....
I got it though. Thanks for the support.
Especially the first reply, I found much inspiration from him.
It involved with the normal count as the first query(as a view)
Then another that had 0 for the select of null(as a view)
Then a union of them both.
Name:
Anonymous2008-10-29 22:48
>>9
You suck massively at SQL. I hope you realise this.
select student.student_id, student_name, count(student.student_id)
from student left join enrolls on
(student.student_id=enrolls.student_id)
group by student.student_id, student_name;
>>9 (as a view)
You did it wrong. Enjoy your poor marks.
Name:
Anonymous2008-10-30 15:22
>>14
SQL is an interesting programming language. I might learn it someday. I don't understand why it's so complicated though, with its GROUP BY and LEFT, RIGHT, CROSS JOINs.
Name:
Anonymous2008-10-30 15:29
Just as a little aside, I hope I'm not derailing the thread here, but how do you guys pronounce SQL?
1) Sequel
2) Squill
3) Sickle/Suckle
4) Ess-queue-elle
>>22
(faggot) only goes behind the correct pronunciation (faggot)
Name:
Anonymous2008-10-30 17:06
>>24
It's actually quite interesting you would mention that. You see, I'm the one who invented the Pronunciation Thread meme. The idea was that the first two would be "wrong-but-plausible" pronunciations, with the third being the correct one but with a (faggot) annotation. The reader would be forced to select the correct option, albeit resign to declaring themselves homosexual. It's actually quite interesting, what's the best recourse in that situation? Do you be correct yet faggot, or do you evade faggotness and be wrong?
These are the questions which keep me up at night.
Name:
Anonymous2008-10-31 1:12
>>25
Interestingly, I'd rather be correct and a faggot, than wrong(and potentially still a faggot anyway.)