CODEWITHSUNDEEP
sqlgroup-by
KhDonen
KhDonen

Reputation: 289

How does the aggregation function work with group by

I do not understand the following (returns numbers of comments for articles with the newest ones dates):

SELECT `id_comment`,COUNT(*) AS `number`, MAX(`date`) AS `newest` 
FROM `page_comments`  
WHERE TO_DAYS( NOW() )-TO_DAYS(`date`) < 90
GROUP BY `id_comment`
ORDER BY `count` DESC,`newest` DESC

I dont understand how come that the MAX function will not return the MAX value of all the page_comments table? That it automatically takes only the max for the given group. When using MAX, I would expect it to return the highest value of the column. I dont understand how it works together with groupig.

Upvotes: 1

Views: 92

Answers (3)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

I am just explaining it to the ground.

MAX() - An aggregate function(Works over the group of data).

If ""group by"" clause is NOT specified, the database implicitly groups the data(column specified) considering the entire result set as group.

If specified, it just groups the data(column) in the group logic specified.

Upvotes: 0

Royi Namir
Royi Namir

Reputation: 148524

It all boils down to analysis order:

  1. FROM
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. CUBE | ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT 10 ORDER BY
  10. TOP

so you first have the from clause

Then you cut the relevant rows via where ( so here your sentence : *I don't understand how come that the MAX function will not return the MAX value of all the page_comments* --fails)

then group it

Then you select it.

The max and aggregate functions apply on the data which is already filtered!

Upvotes: 0

DrCopyPaste
DrCopyPaste

Reputation: 4117

You described the behavior yourself quite correctly already: it automatically takes only the max for the given group.

If you group, you do it (per usual) on every column in the result set, that is not aggregated (not using COUNT, SUM, MIN, MAX...)

That way you get distinct values for all non aggregated columns and the aggregated ones will yield a result that only takes the 'current' group into account.

Upvotes: 1

Related Questions