GROUP BY without aggregate function

by

That’s how GROUP BY works. It takes several rows and turns them into one row. Because of this, it has to know what to do with all the combined rows where there have different values for some columns (fields). This is why you have two options for every field you want to SELECT : Either include it in the GROUP BY clause, or use it in an aggregate function so the system knows how you want to combine the field.

For example, let’s say you have this table:

Name | OrderNumber
------------------
John | 1
John | 2

If you say GROUP BY Name, how will it know which OrderNumber to show in the result? So you either include OrderNumber in group by, which will result in these two rows. Or, you use an aggregate function to show how to handle the OrderNumbers. For example, MAX(OrderNumber), which means the result is John | 2 or SUM(OrderNumber) which means the result is John | 3.

More Related Contents:

  1. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  2. must appear in the GROUP BY clause or be used in an aggregate function
  3. GROUP BY + CASE statement
  4. Using group by on multiple columns
  5. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  6. GROUP BY with MAX(DATE) [duplicate]
  7. Aggregate columns with additional (distinct) filters
  8. PostgreSQL: running count of rows for a query ‘by minute’
  9. Group by minimum value in one field while selecting distinct rows
  10. Create a pivot table with PostgreSQL
  11. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  12. Return multiple columns of the same row as JSON array of objects
  13. Concat groups in SQL Server [duplicate]
  14. SQL query for finding records where count > 1
  15. Best way to count rows by arbitrary time intervals
  16. Compute percents from SUM() in the same SELECT sql query
  17. SQL GROUP BY CASE statement with aggregate function
  18. Mysql: how to select groups having certain values?
  19. MySQL #1140 – Mixing of GROUP columns
  20. MySQL: GROUP_CONCAT with LEFT JOIN
  21. SQL Server Update Group by
  22. Selecting all corresponding fields using MAX and GROUP BY
  23. How can I group time by hour or by 10 minutes?
  24. Huge performance difference when using GROUP BY vs DISTINCT
  25. Postgres window function and group by exception
  26. SQL: difference between PARTITION BY and GROUP BY
  27. SQL not a single-group group function
  28. mysql count group by having
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Truncate timestamp to arbitrary intervals

Leave a Comment