What happens when one aggregates without the ‘group by’ clause?

I was wondering why this SQL script

SELECT category, avg(valuation)
FROM startups
group by 1;

Gives me the same output like this one:

SELECT category, valuation
FROM startups
group by 1;

And why is it possible to group by without any aggregation clause…

Answer

A statement like:

SELECT category, valuation
FROM startups
group by 1;

is allowed in SQLite.
In this case valuation is called a “bare” column (you can find more here: 2. Simple Select Processing).
The result that you get for this column is undefined, meaning it can be any of the values of valuation of the category.

I highly doubt that you get the same results as:

SELECT category, avg(valuation)
FROM startups
group by 1;

which is a valid SQL statement and returns the average of valuation for each category.
Unless all the valuations for each category are the same then the similarity in the results is coincidental.

See a demo.