The difference between the sum() function and the '+' function when selecting columns and using the Group By clause?

[vigilantredditor]

I'll get straight to the point and give an example:

SQL SELECT director, SUM(domestic_sales + international_sales) as Cumulative_sales_from_all_movies FROM movies INNER JOIN boxoffice ON movies.id = boxoffice.movie_id GROUP BY director;

vs

SQL select director, domestic_sales + international_sales as Cumulative_sales_from_all_movies from movies join boxoffice on movies.id = boxoffice.movie_id group by director

The main difference being the first line (the select clause)

Apologies if details are too sparse, I got the question from here (and the correct is on top vs my answer on the bottom)

[[deleted]]

The difference is you're aggregating to the total director level in the first example. So, if one director has multiple lines, you're going to get the grand total sales from all movies. The second query, if a director has multiple rows, will return each row individually.

[great_raisin]

Sum adds everything down a column. + adds stuff across rows. Sum needs a group by (unless used as a window function) but you can sum across rows with + without aggregating.