most efficient SQL query

Question

I have 2 tables:

T1 {
  id: string;
  school: string
}

and

T2 { 
  school_id: string; 
  student_id: string; 
  tuition_fee: double 
}

The 2 tables are associated by T1.id and T2.school_id. T1 contains information of each school. It's relatively small. T2 contains information of the tuition fee for each student. It can be huge (millions of records).

Edit:

Now, I want to find out which 3 schools collect most tuition fees from its students. Assume that it's impossible that 2 schools collect the same amount of total tuition fees from all its students. And my solution is as follow:

Group records in T2 by school_id;
Compute sum of tuition_fee in each group;
Sort the groups by the sum in descending order;
Get the top 3.

My questions are:

Any better way to do this?
What's the most efficient SQL to do this?
Any other consideration to take into account to improve the speed? e.g. using indexing, view, etc.

Your method is fine. It should all be done in a single query. — Gordon Linoff, Dec 30 '15 at 19:45
@GordonLinoff, my intuition tells me that sorting in step 3 might not be needed... — james, Dec 30 '15 at 19:47
Are you sure you really have the question right? There may be ties for tuition fees. Even, more than three schools for the top tuition. What are you supposed to return then? — philipxy, Dec 30 '15 at 19:49

score 2 · Answer 1 · edited May 23 '17 at 12:23

If you only want the school_id and the sum of the fee one option is:

SELECT TOP 3
  SUM(tuition_fee) AS TOTAL_TUITION_FEE,
  school_id
FROM T2
GROUP BY school_id
ORDER BY TOTAL_TUITION_FEE DESC

If you´re using SQL server you can use the query analyzer (some info of where to find it) for seeing how to improve the performance, it suggest index or others tips.

Don't seem to be a pretty heavy query.

most efficient SQL query

1 Answers1