Using Max and Sum for the same column without sub-query -Mysql

Question

I am new to Mysql, I wanted to use as sum (Max (column))?

I do not know why it is impossible to use?

whereas count (Distinct (column)) is possible using two aggregate functions together

Answer 1

The reason is that SUM () and MAX () are cumulative functions, and these functions return results by grouping their inputs through a table. When applying one aggregate function over another, it is similar to grouping data by the result set of another grouping function, and this is not allowed in SQL.

But in SQL Server since the 2008 version, a new OVER () clause has appeared so that we can specify the grouping criteria in this above section for this particular column.

In the case of DISTINCT, which does not need any grouping, and it pulls out a separate set of records over which we apply the aggregate function. So it will work.

Answer 2

You can do as follows, but if you do not use the columns of the orther grouping, it always returns 1 column, so SUM (MAX (tot)) = MAX (tot), although we cannot do this in one place:

SELECT SUM(total) tot FROM ( SELECT MAX(quantity) total FROM deliveries) t; 

Answer 3

DISTINCT not an aggregate function. This is a keyword core team.

In your case, when you get MAX from a group, you get one value. SUM one meaning does not make sense.

If you want SUM MAX values ​​from all existing groups, you can try the following:

 SELECT SUM(<column>) FROM <table> t WHERE NOT EXISTS ( -- filter all max values from each group SELECT 1 FROM <table> d WHERE 1=1 AND d.<group_column1> = t.<group_column1> AND d.<group_column2> = t.<group_column2> ... AND d.<column> > t.<column> )