3 Replies - 747 Views - Last Post: 27 February 2017 - 04:46 PM Rate Topic: -----

#1 Totte  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 18-January 16

Having issues with a query :)

Posted 25 February 2017 - 08:20 AM

[PostgreSQL] I want to select the course(s) which have more students than the average amount of students on all courses. Formula would be: (amount of students in course) > (total amount of students / number of unique course anyone have taken) I guess.
This is how the table looks like:

   id     |coursecode| grade |
------------------------------
joan1140  |  SA121G  |   2   |
joan1140  |  DF522G  |   1   |
joan1140  |  AF021P  |   4   |
kaqe1297  |  DF522G  |   2   |
tawe1010  |  SA121G  |   2   |
tawe1010  |  DF522G  |   4   |
arge1501  |  FA071M  |   3   |
------------------------------


I got the following before I went completely stuck...

SELECT coursecode, COUNT(coursecode) AS amount FROM table.grade
GROUP BY coursecode
HAVING COUNT(id) > ((SELECT COUNT(DISTINCT id) FROM table.grade WHERE grade IS NOT NULL) / (SELECT COUNT(DISTINCT coursecode) FROM table.grade WHERE grade IS NOT NULL));

Thanks! Im really stuck on this one :surrender:/>/>

This post has been edited by Totte: 25 February 2017 - 08:43 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Having issues with a query :)

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2330
  • View blog
  • Posts: 9,381
  • Joined: 03-December 12

Re: Having issues with a query :)

Posted 25 February 2017 - 08:28 AM

Might help if we know the specific database you are using as well.
Was This Post Helpful? 0
  • +
  • -

#3 Totte  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 62
  • Joined: 18-January 16

Re: Having issues with a query :)

Posted 25 February 2017 - 08:43 AM

View Postastonecipher, on 25 February 2017 - 08:28 AM, said:

Might help if we know the specific database you are using as well.


Oh, sorry :) PostgreSQL
Was This Post Helpful? 0
  • +
  • -

#4 mojo666  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 408
  • View blog
  • Posts: 882
  • Joined: 27-June 09

Re: Having issues with a query :)

Posted 27 February 2017 - 04:46 PM

Quote

Formula would be: (amount of students in course) > (total amount of students / number of unique course anyone have taken) I guess.


If I'm understanding the assignment correctly, I don't think you want "total amount of students". To get "Average students per course" you just take the total number of entries in the table and divide by the number of unique courses.

You have 7 entries and 4 courses which is an average of 1.75 students per course. Thus you want your query to select SA121G and DF522G as they have 2 and 3 students enrolled respectively while the other courses only have 1.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1