SQL query help

[Jumpem]

This is a simple query, but I can't think tonight. I have a table with two columns... referralNum and studentNum. I want to count how many different referral's each student has. Thanks for any help.🙂

 

[Descartes]

Presuming the relationship between the student and referral table is a 1:M, you would do this:

select st.studentNum, count(*) as totalReferrals
from studentTable st
inner join referralTable rt
on st.studentNum = rt.studentNum
group by st.studentNum

 

[Firus]

would it not work to do...

select studentNum, count(distinct(referralNum))
from studentTable
group by studentNum;

I threw together a quick table to test this on and it worked...

that is if you need each 'distinct' referral number, but if you just need the number of referral numbers for each student, leave out the 'distinct'

 

[Descartes]

Originally posted by: Firus
would it not work to do...

select studentNum, count(distinct(referralNum))
from studentTable
group by studentNum;

I threw together a quick table to test this on and it worked...

that is if you need each 'distinct' referral number, but if you just need the number of referral numbers for each student, leave out the 'distinct'

The referralNum should be in the referral table, so the above wouldn't work. You could do this:

select studentNum, count(*)
from referralTable
group by studentNum

But that doesn't explicitly state the relationship between the studentTable and referralTable. That's why I added the join; if he didn't have a FK on the studentNum there's a chance you'd have nonexistent students being counted.

 

[Entity]

You have one table with two columns, or multiple tables?

If you only have one table, then Firus' suggestion should be what you need (minus the distinct):

select studentNum, count(referralNum)
from studentTable
group by studentNum;

Rob

 

[Firus]

Originally posted by: Jumpem
I have a table with two columns... referralNum and studentNum. I

I'm assuming by this statement that he has one table two columns

 

[Jumpem]

Originally posted by: Entity
You have one table with two columns, or multiple tables?

If you only have one table, then Firus' suggestion should be what you need (minus the distinct):

select studentNum, count(referralNum)
from studentTable
group by studentNum;

Rob

Yeah, it is ony a single table that I am interested in. Firus' query seems to have worked... I just need to check.

 

[TechBoyJK]

hey guys dont over complicate things, this is a simple record count.