Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

kevin nadjarian
kevin nadjarian
1,498 Points
Posted by kevin nadjarian
kevin nadjarian
1,498 Points

COUNT(*) result change

Why the "SELECT TEACHERS.ID, FIRST_NAME, LAST_NAME, COUNT() FROM TEACHERS" Change the result and only display one row, while the same query without the COUNT() Display multiple rows?

Also the actual question on this was "Which teachers teach a class during all 7 periods?" If we just add COUNT(*) from the current results, that just getting the count for every teachers and every period_ID and not only the teacher which teach on each periods ?

I'm lost....

1 Answer

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

"COUNT" is an aggregate function. This means it operates on the entire result set and returns a single row. The other values are just picked from some single row in the set.

As stated in the SQLite docmentation:

There are two types of simple SELECT statement - aggregate and non-aggregate queries. A simple SELECT statement is an aggregate query if it contains either a GROUP BY clause or one or more aggregate functions in the result-set. Otherwise, if a simple SELECT contains no aggregate functions or a GROUP BY clause, it is a non-aggregate query.

If the SELECT statement is an aggregate query without a GROUP BY clause, then each aggregate expression in the result-set is evaluated once across the entire dataset. Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression. Or, if the dataset contains zero rows, then each non-aggregate expression is evaluated against a row consisting entirely of NULL values.

An aggregate query without a GROUP BY clause always returns exactly one row of data, even if there are zero rows of input data.