CODEWITHSUNDEEP
sqldatabasepostgresqlgreatest-n-per-group
MrCokeman
MrCokeman

Reputation: 73

Comparing two values in Postgres in one query

So here is my query so far:

Select 
    class.title, studentClass.grade, count(studentClass.grade) 
from 
    classOffering 
inner join 
    studentClass on classOffering.classID = studentClass.classID
inner join 
    class on classOffering.classID = class.classID
group by 
    class.title, studentClass.grade
order by 
    count(studentClass.grade) desc

And here is the output:

enter image description here

Now what I am trying to do is only get back the most frequent grade for a class. So I need to cut out software development I with the grade of a B, because A is the most frequent grade in the class. But I do not know how to compare the count values. Any help would be wonderful.

Upvotes: 4

Views: 3172

Answers (4)

MrCokeman
MrCokeman

Reputation: 73

Select tableView.title, tableView.grade from (Select class.title as title, studentClass.grade as grade, count(studentClass.grade) as count from classOffering 
inner join studentClass on classOffering.classID = studentClass.classID
inner join class on classOffering.classID = class.classID
group by class.title, studentClass.grade
order by count(studentClass.grade) desc)as tableView group by tableView.title, tableView.grade;

Upvotes: 0

Raviiii
Raviiii

Reputation: 1

WITH tableView1 as (Select class.title as title, studentClass.grade as grade, count(studentClass.grade) as count from classOffering inner join studentClass on classOffering.classID = studentClass.classID inner join class on classOffering.classID = class.classID group by class.title, studentClass.grade order by count(studentClass.grade) desc),
tableView2 as (select title,max(count) as count from tableView1 group by title),
tableView3 as (select tableView1.title as title,tableView1.grade as grade,tableView1.count as count from tableView1 inner join tableView2 on tableView1.title=tableView2.title AND tableView1.count=tableView2.count) select * from tableView3 order by title,grade,count;

Upvotes: 0

cableload
cableload

Reputation: 4375

If you dont need the count (based on your comments) you could try something like this

select a.title,a.grade from 
(Select class.title, studentClass.grade, 
row_number() over (partition by class.title order by studentClass.grade) as rn 
from classOffering inner join studentClass on classOffering.classID = studentClass.classID
inner join class on classOffering.classID = class.classID
group by class.title, studentClass.grade)a
where a.rn=1;

The below query will also get the count

select a.title,a.grade,a.gradeCount from 
    (Select class.title, studentClass.grade,
     count(studentClass.grade) over (partition by class.title) as gradeCount 
    row_number() over (partition by class.title order by studentClass.grade) as rn 
    from classOffering inner join studentClass on classOffering.classID = studentClass.classID
    inner join class on classOffering.classID = class.classID
    group by class.title, studentClass.grade)a
    where a.rn=1;

Upvotes: 1

jwatts1980
jwatts1980

Reputation: 7346

Another common solution to this problem is joining the table back onto itself. Here is a SQL Fiddle that tests this answer:

SELECT T3.title, T3.grade, T2.maxcount
FROM (
    SELECT title, max(count) as maxcount
    FROM (
        Select class.title, studentClass.grade, count(studentClass.grade) as count
        from classOffering 
        inner join studentClass on classOffering.classID = studentClass.classID
        inner join class on classOffering.classID = class.classID
        group by class.title, studentClass.grade
    ) AS T1
    GROUP BY title
) AS T2 
JOIN (
    Select class.title, studentClass.grade, count(studentClass.grade) as count
    from classOffering 
    inner join studentClass on classOffering.classID = studentClass.classID
    inner join class on classOffering.classID = class.classID
    group by class.title, studentClass.grade
) AS T3 ON T2.title = T3.title AND T2.maxcount = T3.count
ORDER BY T3.title, T3.grade, T2.maxcount

But I think @cableload 's answer is much better if it can be tweaked to consider the max number of occurrences of the grade.

Upvotes: 0

Related Questions