Why can’t you mix Aggregate values and Non-Aggregate values in a single SELECT?

by

Aggregates doesn’t work on a complete result, they only work on a group in a result.

Consider a table containing:

Person   Pet
-------- --------
Amy      Cat
Amy      Dog
Amy      Canary
Dave     Dog
Susan    Snake
Susan    Spider

If you use a query that groups on Person, it will divide the data into these groups:

Amy:
  Amy    Cat
  Amy    Dog
  Amy    Canary
Dave:
  Dave   Dog
Susan:
  Susan  Snake
  Susan  Spider

If you use an aggreage, for exmple the count aggregate, it will produce one result for each group:

Amy:
  Amy    Cat
  Amy    Dog
  Amy    Canary    count(*) = 3
Dave:
  Dave   Dog       count(*) = 1
Susan:
  Susan  Snake
  Susan  Spider    count(*) = 2

So, the query select Person, count(*) from People group by Person gives you one record for each group:

Amy    3
Dave   1
Susan  2

If you try to get the Pet field in the result also, that doesn’t work because there may be multiple values for that field in each group.

(Some databases, like MySQL, does allow that anyway, and just returns any random value from within the group, and it’s your responsibility to know if the result is sensible or not.)

If you use an aggregate, but doesn’t specify any grouping, the query will still be grouped, and the entire result is a single group. So the query select count(*) from Person will create a single group containing all records, and the aggregate can count the records in that group. The result contains one row from each group, and as there is only one group, there will be one row in the result.

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. Optimal way to concatenate/aggregate strings
  3. PostgreSQL: running count of rows for a query ‘by minute’
  4. LISTAGG in Oracle to return distinct values
  5. must appear in the GROUP BY clause or be used in an aggregate function
  6. Two SQL LEFT JOINS produce incorrect result
  7. Create a pivot table with PostgreSQL
  8. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  9. Return multiple columns of the same row as JSON array of objects
  10. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  11. How to include “zero” / “0” results in COUNT aggregate?
  12. GROUP BY + CASE statement
  13. Best way to count rows by arbitrary time intervals
  14. Selecting data into a Postgres array
  15. Compute percents from SUM() in the same SELECT sql query
  16. Get the distinct sum of a joined table column
  17. The SQL OVER() clause – when and why is it useful?
  18. Group DateTime into 5,15,30 and 60 minute intervals
  19. Grouped string aggregation / LISTAGG for SQL Server
  20. Aggregate a single column in query with many columns
  21. MySQL: GROUP_CONCAT with LEFT JOIN
  22. Multiple array_agg() calls in a single query
  23. Custom aggregate function (concat) in SQL Server
  24. Postgres window function and group by exception
  25. SQL: difference between PARTITION BY and GROUP BY
  26. GROUP BY without aggregate function
  27. Count cumulative total in Postgresql
  28. How to combine aggregate functions in MySQL?
  29. Truncate timestamp to arbitrary intervals
  30. Why are aggregate functions not allowed in where clause

Leave a Comment