CODEWITHSUNDEEP
sqloracle-database
Ryan Huffman
Ryan Huffman

Reputation: 21

SQL query to show minimum average along with title using two tables

I have two tables:

huffman_students (
id (PK),
fname,
lname,
status ("freshman", "sophomore", "junior", "senior"),
majorcode (FK - huffman_departments.deptcode),
gpa,
admittedDate)

huffman_departments (
deptcode (PK),
deptname,
college)

I need to show the college the lowest average GPA.

I have been able to come up with this query:

SELECT MIN(avg_gpa)
FROM
(
SELECT d.college, AVG(s.gpa)
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
);

Which gives the output:

MIN(AVG_GPA)
------------
       3.436

I need output that looks like:

COLLEGE    MIN(AVG_GPA)
-----------------------
INF               3.436

I have tried a lot of different queries and keep getting errors. A common error that I am getting when trying to show the college that lowest average gpa belongs to is: "not a single-group group function."

I am using Oracle SQL Plus.

Upvotes: 2

Views: 438

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

If you are looking for one row, you can use ORDER BY and FETCH FIRST 1 ROW ONLY:

SELECT d.college, AVG(s.gpa)
FROM huffman_departments d JOIN
     huffman_students s
     ON s.majorcode = d.deptcode
GROUP BY d.college
ORDER BY AVG(s.gpa)
FETCH FIRST 1 ROW ONLY;

If you want to see multiple rows in the event of ties, there are multiple approaches. In MySQL 8+, window functions are probably the simplest method. In earlier versions, double aggregation may be the simplest:

SELECT avg_gpa, LISTAGG(college, ',') WITHIN GROUP (ORDER BY college) as colleges
FROM (SELECT d.college, AVG(s.gpa) as avg_gpa
      FROM huffman_departments d JOIN
           huffman_students s
           ON s.majorcode = d.deptcode
      GROUP BY d.college
     ) c
ORDER BY avg_gpa
FETCH FIRST 1 ROW ONLY;

EDIT:

Older versions of Oracle do not support FETCH FIRST. So you can do:

SELECT college, avg_gpa
FROM (SELECT d.college, AVG(s.gpa) as avg_gpa,
             ROW_NUMBER() OVER (ORDER BY AVG(s.gpa)) as seqnum
      FROM huffman_departments d JOIN
           huffman_students s
           ON s.majorcode = d.deptcode
      GROUP BY d.college
     ) c
WHERE seqnum = 1
ORDER BY AVG(s.gpa);

Upvotes: 1

Peter Szalay
Peter Szalay

Reputation: 386

Using what you already have:

SELECT b.*
FROM  (SELECT d.college, AVG(s.gpa) avg_gpa
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
) b
WHERE b.avg_gpa = (SELECT MIN(a.avg_gpa)
FROM
(
SELECT d.college, AVG(s.gpa) avg_gpa
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
) a);

Upvotes: 0

GMB
GMB

Reputation: 222582

You could move the existing query to a subquery (or a CTE), and then use analytic function ROW_NUMBER to filter the relevant record :

WITH cte AS (
    SELECT d.college, AVG(s.gpa) avg_spa
    FROM 
        huffman_departments d
        INNER JOIN huffman_students s ON s.majorcode = d.deptcode
    GROUP BY d.college
)
SELECT *
FROM (
    SELECT c.*, ROW_NUMBER() OVER(ORDER BY avg_spa) rn
    FROM cte c
) x WHERE rn = 1

PS : always use explicit JOINs instead of implicit (CROSS JOIN + WHERE clause). I changed the query accordingly.

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

you can use Common Table Expressions

with cte0 as 
(
  SELECT d.college, AVG(s.gpa) agpa
  FROM huffman_departments d, huffman_students s
  WHERE s.majorcode = d.deptcode
  GROUP BY d.college
)
select * from cte0  c
where c.college (
  select ci.college from cte0 ci
  group by ci.college 
  having min(ci.agpa) = a.gpa
)

if you are using mysql

SELECT d.college, AVG(s.gpa)
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
order by 2 asc
limit 1

Upvotes: 1

Related Questions