How to Query this???Heeeelp

# Thread: How to Query this???Heeeelp

1. Senior Member
Join Date
Dec 1969
Posts
236

## How to Query this???Heeeelp

i have to display total ponts for each student based on their ranks.<BR>The calculation for rank is as follows<BR>1 = 3 points<BR>2 = 2 points <BR>3 = 1 point<BR><BR>I have Student table w/ student details and PK as StudenID<BR>Then I have Results table as follows<BR>ResultID(PK)&#124 StudentID(FK)&#124 Rank<BR>1 1 3<BR>2 2 2<BR>3 1 3<BR>4 2 2<BR>5 3 1<BR>6 1 2<BR><BR>** A student can have same rank more than one times as i am collecting ranks for differet subjects.<BR><BR>Now my o/p should look like this<BR>Student ID &#124 Rank &#124 times &#124 total points <BR>1 3 2 2<BR>1 2 1 2<BR><BR>2 2 2 4<BR><BR>3 1 1 3<BR> <BR>Any help would be greatly appreciated .<BR>Thanks!

2. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## RE: How to Query this???Heeeelp

hmmm<BR><BR><BR>jsut a quick dab<BR><BR>select StudentID, Rank, count(Rank) from Results <BR>group by StudentID, Rank<BR><BR><BR>now for your total points i would jsut multiply that in the front end<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
236

## RE: How to Query this???Heeeelp

But i have to display the records in descending order based on total points, hence i need to find the total points in the backend itself.

4. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Oh ok

the a simple case statement should do it<BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
236

## RE: This is what i did .....BUT.....

select distinct a.*,count(b.rank),<BR>Case b.Rank<BR>When 3 then count(b.Rank)*5 <BR>When 2 then count(b.Rank)*3 <BR>When 1 then count(b.Rank)*1 <BR>End as TotalRankPoints<BR>from Students a, Results b where a.StudentId = b.StudentID group by a.studentid,b.rank,a.studentfname,a.studentLname,a .Datecreated <BR><BR>But this will break down the rank and give me total points of each rank.<BR>but i still need to find the whole total points by each student and order by the whole total points <BR><BR>Any clue?? thanks!<BR>

6. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## What?

you cant get get the rank AND the total points for all the ranks<BR><BR><BR>pick one you cant have both<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## And you also can NOT mix...

...DISTINCT and GROUP BY. Never works.<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•