How can I sum values in a MySQL query?

Question

I am using MySQL.
I have a P_id that should be able to find the max value. I want to show the sum of two formulas by following:

    Then 

          Sum(max(pval)*S_val*Estimate)
          Sum(max(pval)*P_val*Analyze)
    Finally sum the Both above

I used the following function but it gives me an error:

               
>    id Display P_id P_val Analyze S_id S_val Estimate    
>    70 Data1   1       1       178     0       0       0    
>    71 Data2   1       0       0       1       3       50  

SELECT  SUM( max(pval)*S_val*Estimate) + Sum( max(pval)* P_val * Analyze) from DATA where pid='1'

This results in:

• A: 1*178
• B: 1*3*50
• Sum(A+B): 328

Answer 1

You cannot nest aggregate functions - SELECT SUM(MAX(x)) will always error.

You can get the inner value in a subquery instead:

SELECT
  SUM(maxpval * S_val * Estimate),
  SUM(maxpval * P_val * Analyze)
FROM
  yourTable
  JOIN (
    SELECT MAX(P_val) AS maxpval FROM yourTable
  ) AS dt;