CODEWITHSUNDEEP
mysql
user3420515
user3420515

Reputation: 1

How to use SQL MAX(SUM()) function

Hi this is driving me crazy consultation, such as selecting the maximum of this consultation, I made a temporary table to which the maximum then sack him, but quiciera know if the same query can be removed without temporary tables.

I have 2 tables N AND ACCRUED PAYROLL D

Table I occupy the payroll payroll 201314-201320 and table I need the EMPLOYEEIDNO accrued; cod_tiponomina; cod_nomina and accrued employee wages are earned in 14 days;

Good help is that I need to get the maximum of the salary calculation with the sum ();

thanks for the help.

SELECT MAX((SUM(d.devengado) / (COUNT(d.devengado)*14))*30) salario_30dias 
  FROM devengados d 
  JOIN nominas n 
    ON n.cod_nomina = d.cod_nomina 
 WHERE d.cod_empleado = 564 
   AND d.cod_tiponomina = 1 
   AND d.cod_nomina BETWEEN 201314 AND 201320 
   AND d.devengado > 0 
 GROUP 
    BY YEAR(n.fecha_cierre) 
     , MONTH(n.fecha_cierre);

Upvotes: 0

Views: 2314

Answers (1)

dognose
dognose

Reputation: 20909

'Max' and 'Sum' are always executed according to the Group By Clause.

You can nest 2 Selects to get the Maximum of Different Sums:

SELECT MAX(sum) FROM 
  (SELECT SUM(column) AS sum FROM table GROUP BY crit1) 
GROUP BY sum

But a better Way would be to sort the sums, and pick the first one to achieve the same as a sourrounding MIN/MAX (It would not require the nesting of selects):

SELECT SUM(column) AS sum FROM table GROUP BY crit1 ORDER BY sum DESC LIMIT 0,1

(for MIN you would need to sort ASC)

Upvotes: 1

Related Questions