henryzo henryzo - 7 months ago 15
SQL Question

sql query confusion with count

im trying to fgure out this query from a sql book im reading.


  1. Find the name of the most popular course based on the number of the times the course was offered.



i have the query up to were it lists the courses and counts how many times each has been offered, now my question is how do satisfy the question above lol. how can i get just the course that was offered the most ? (ie. the databases course)

my query is

SELECT
count(s.course_id) AS Times_Offered,
c.title
FROM
sections s
INNER JOIN courses c ON c.id = s.course_id
GROUP BY c.title
ORDER BY Times_Offered DESC;


my query shows up as

times_offered title
3 Databases
3 Calculus 1
2 Compilers
2 Elocution
2 Acting
1 Topology

Answer

If you want to count them and then show the one with the highest number only, try this:

SELECT COUNT(s.course_id) 
AS Times_Offered, c.title FROM sections s 
INNER JOIN courses c ON c.id=s.course_id 
GROUP BY c.title 
ORDER BY Times_Offered DESC
LIMIT 1