SQL Select Statement with different criteria

Transact-SQL
1

Question_table.PNG511×556 9.54 KB

SELECT subject_name,
test1, test2, total, grad FROM
hs_results JOIN
hs_subjects AS s USING(subject_id) WHERE admin_no =2100 AND
subject_id IN ( SELECT subject_id
FROM hs_subjects WHERE class_id =4 AND subject_name NOT IN ('mathematics',
'English Language') )ORDER BY total DESC LIMIT 5
UNION SELECT
subject_name, test1, test2, total, grad
FROM hs_subjects WHERE class_id =4 AND subject_name IN ('mathematics',
'English Language')

Please, I want to join these two tables and select seven (7)
best subjects including English Language and Mathematics. Out of Nine subjects.
I need help.

2

I belive it is generally a bad idea to filter on id's, and looking at your query, you filter on class_id in the subjects table. You probably have a class table that this id points to, so you might want to join in this table and then filter on the name of the class instead.

But that was not what you're asking - actually I'm not exactly sure, what it is, you're asking!
Are you saying, you want to see:

  • 7 results in total
  • 2 results must always be Mathematics and English Language
  • 5 results must be highest score (total) excluding Mathematics and English (as they are always shown)
select top(7)
       a.subject_name
      ,b.test1
      ,b.test2
      ,b.total
      ,b.grad
  from subjects as a
       inner join results as b
               on b.subject_id=a.subject_id
              and b.admin_no=2100
 where a.class_id=4 /* <--- bad idea */
 order by case when a.subject_name in ('English Language','Mathematics') then 0 else 1 end
         ,b.total desc
;

Now - the above query works on Microsoft SQL Server, but it seems you might be using MySQL. If so, the top statement won't work - You'd have to use limit instead.

1 Like
4

Thank you so much.
Can you help me restructure the query below to count only the best 7 subjects in a class.
It's counting all the 9 subjects.

SELECT admin_no, ROUND(SUM(total) / COUNT(result_id),2) AS avg_score, COUNT(result_id) FROM results WHERE
class_id= 4 GROUP BY admin_no ORDER BY avg_score DESC

5

If you're using Microsoft SQL Server, use top:

SELECT top(7)
       admin_no
      ,ROUND(SUM(total)/COUNT(result_id),2) AS avg_score
      ,COUNT(result_id)
  FROM results
 WHERE class_id=4
 GROUP BY admin_no
 ORDER BY avg_score DESC
;

If you're using MySQL, use limit:

SELECT admin_no
      ,ROUND(SUM(total)/COUNT(result_id),2) AS avg_score
      ,COUNT(result_id)
  FROM results
 WHERE class_id=4
 GROUP BY admin_no
 ORDER BY avg_score DESC
 limit 7
;