help needed with query

Question

help needed with query

hlsc1983

SSCommitted

Points: 1971
More actions
April 4, 2016 at 8:52 am

#312911

i have a table with the following columns:
StudentId,
StudentName,
DateofBirth,
Category.
i need to find out the number of distinct Studentnames in each category. Please help

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up COUNT(DISTINCT...) and GROUP BY in SQL's documentation (called Books Online)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hlsc1983 SSCommitted Points: 1971 More actions · Answer 2

thanks. i have tried this but i am not getting the student count just the scholarship_id

SELECT scholarship_id, Count(Distinct(scholarship_id)) as [Count]

FROM Students

Group By scholarship_id

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That's not what you asked for. The original question was to count the StudentName (the column in the count) per category (the group by), not count the unique scholarships in each scholarship (which, if you think about it, doesn't produce anything useful)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hlsc1983 SSCommitted Points: 1971 More actions · Answer 4

oh yes... i need to count the no of distinct student names in each category. actually it should have been scholarship)id and not category.

i tried that query because it seemed simpler and i thought if i got it then it would help me get the next one as well. apologies for not explaining myself

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ok, so you want to count() the student names and group by the scholarship

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hlsc1983 SSCommitted Points: 1971 More actions · Answer 6

yes count the distinct studentnames and group by category. i want the result to be like this

no . | category

------------------------

100 | A

200 | B

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Ok, so write the query. You had it almost right and you've had enough hints as to how to correct it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hlsc1983 SSCommitted Points: 1971 More actions · Answer 8

got it!

SELECT scholarship_id , Count(Distinct student_fullname ) as [No of students]

FROM Students

Group By scholarship_id

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

hlsc1983 (4/4/2016)
got it!
SELECT scholarship_id , Count(Distinct student_fullname ) as [No of students]
FROM Students
Group By scholarship_id

That's going to be a problem if there's more than one student with the same fullname. What's the PK on the Students table?

Also, always using two part naming on all tables is a very good habit to get into. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)