Why does the subquery with count(*) return multiple values?

Question

I have a student table and address table. I want to find out what percentage of them come from London. I have a query that looks like

SELECT (SELECT COUNT(*) FROM student
INNER JOIN address on student.address_id = address.id 
WHERE city = 'Lodon')/ COUNT(*)  FROM student

When I run this query I get an error

SELECT (SELECT COUNT(*) FROM student ERROR at line 1: ORA-00937: not a single-group group function

Why is this happening and how can I fix it? When I run the subquery separately it returns a single value, but why does it return multiple values when I put in a subquery? Also, if there is a cleaner query please suggest it.

Gordon Linoff · Accepted Answer · 2021-01-05 22:00:03Z

1

Use conditional aggregation:

SELECT AVG( CASE WHEN a.city = 'London' THEN 1.0 ELSE 0 END)
FROM student s JOIN
     address a
     ON a.address_id = s.id ;

Your query returns an error because subqueries with aggregation queries are tricky.

answered Jan 5, 2021 at 22:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why does the subquery with count(*) return multiple values?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related