This is  a tricky behavior of SUM() function which is going to be tested for sure on the exam;

We perceive the main purpose of SUM() function as a summation of all elements it may be applied on. But two almost identical SUM() function usages will give you the different results, depends on their context. Let’s compare how SELECT SUM(1) FROM ORDERS will differ from SELECT SUM(DISTINCT 1) FROM ORDERS. At first glance, they are identical, because DISTINCT 1 will always give you 1, so the second query will have the same argument as query 1, therefore the results produced in both cases might be the same. But, it is not happening. Let’s consider why

As we can see the query above adds 1 across 105 rows and returns 105. Should we add 2 across 105 rows, then we would get 210 ( see below  )

And so on. Should we add 3 across 105 rows from the ORDERS table, then we would get 315.

This is happening because SELECT SUM(1) FROM ORDERS actually count the rows in ORDER table by adding 1 across 105 rows,  rather than do a summation. Same for SELECT SUM(2) FROM ORDERS, it adds 2 across all 105 rows. So, in these cases, the argument of SUM() function is a single numeric literal defined explicitly.  In this case, SUM() functions won’t do a summation, it rather works as described above – add this single numeric literal  across all rows in the table. This behavior is a little strange, but this is how SUM() will behave in case of single numeric literal defined explicitly.

Now, let’s consider another query which is slightly different from the previous one. Let’s examine how SELECT SUM(DISTINCT 1) FROM ORDERS WILL WORK;

As we can see, adding DISTINCT to SUM() function argument will change the result produced by SUM() function. Now it produced 1. Why is it happening? SELECT DISTINCT 1 FROM ORDERS will give you 1, eventually, it will be the same query as before SELECT SUM(1) FROM ORDERS, so we may expect the same result. But it is not happening. Why? The answer is: because this time the argument was not defined as a single numeric literal defined explicitly. This time the argument for SUM() function was calculated before SUM() function can get and use it. Therefore, this time the argument was perceived by SUM() function as expected.

You may get this situation on the exam. Just remember the rule: if you see a single numeric literal defined explicitly within the SUM() function, then it will add this literal across all rows and returns the result, rather than return this literal as a SUM