syntax of nested sql aggregate functions

662 views sql
-1

I'm learning sql on codecademy

There's an example of nested aggregate function:

SELECT ROUND(AVG(price), 2)
FROM fake_apps;

ROUND() requires a number as first parameter, how can AVG(price) be plugged in here? What's the datatype of it?

If I change the code to:

SELECT ROUND(SELECT AVG(price)
             FROM fake_apps, 2)
FROM fake_apps;

It gives syntax error.

answered question

Your first query should work, so what is the problem?

No worreis mate it's been solved.

2 Answers

13

The below code gives a syntax error because ROUND is a scalar function. It expects exactly one value as the first parameter.

SELECT ROUND(SELECT AVG(price)
             FROM fake_apps, 2)
FROM fake_apps;

SELECT AVG(price) FROM fake_apps returns a set of rows.

On the other hand, AVG is an aggregate function. It operates on a set of rows. The return type is numeric.

posted this
6

I don't think it needs nested select query, at least as per your requirement example you have provided. This may be resolved with following query also.

SELECT ROUND(  AVG (price), 2 ) FROM fake_apps

DataType should be decimal or numeric with precision (for e.g. numeric (18,3) )
Let me know if I interpreted as wrong question and correct me, so I can try another way.

posted this

Have an answer?

JD

Please login first before posting an answer.