Question in the title. Thanks for the time.

EXAMPLE v

SELECT
  customer_id,
  SUM(unit_price * quantity) AS total_price
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
GROUP BY customer_id

CodePudding user response：

Yes, grouping does go hand in hand with aggregate functions, for any resulting column not contained within an aggregate function

CodePudding user response：

Grouping and the operations of aggregation are typically linked to three keywords:

• any aggregation function
• the GROUP BY clause
• the DISTINCT (ON) modifier after the SELECT keyword

You can use:

• aggregation functions alone when they are used on every field found inside the SELECT statement
• the GROUP BY clause alone - an allowed bad practice as you're intending to do an aggregation on your field but you're not specifying any aggregation function inside the SELECT statement (really you should go with DISTINCT ON)
• the DISTINCT modifier alone to select distinct rows (not fields)
• the DISTINCT ON modifier only when accompanied by an ORDER BY clause that defines the order for extracting the rows
• aggregation functions the GROUP BY clause, that is forced to contain all fields found in the SELECT statement that are not object of aggregation
• the DISTINCT modifier the GROUP BY clause - you can do it but the GROUP BY clause really is superfluous as it is already implied by the DISTINCT keyword itself.

You can't use:

• aggregation functions the GROUP BY clause when non-aggregated fields included in the SELECT statement are not found within the GROUP BY clause
• aggregation functions alone when in the SELECT statement they are found together with non-aggregated fields.

When you can't aggregate because you need to select multiple fields, typically window functions filtering operations (with a WHERE clause) can come in handy for computing values and row by row and removing unneeded records.