How to choose the amount (max left (x + y + z)) from MySQL to get the amount (1200 + 1300 + 1400)

Question

How to select sum(max remain(x + y + z)) from MySQL to catch sum(1200+1300+1400) ?

 id | user | remain ----------------- 1 | x | 1000 ---------------- 2 | x | 1200 ---------------- 3 | y | 1100 ---------------- 4 | y | 1300 ---------------- 5 | z | 1200 ---------------- 5 | z | 1400 ---------------- using (SqlConnection cn = new SqlConnection(Class1.x)) { cn.Open(); string cm1 = "select sum(max remain(all users)) as 'total' from item_new_company"; using (SqlCommand cmd = new SqlCommand(cm1, cn)) { using (SqlDataReader dr = cmd.ExecuteReader()) { dr.Read(); tot5 = dr["total"].ToString(); } } } 

Answer 1

Like this:

 SELECT SUM(MaxRemain) TotalOfMaxRemains FROM ( SELECT MAX(remain) AS MaxRemain FROM item_new_company GROUP BY user ) AS t; 

SQL Fiddle Demo

Result:

 | TOTALOFMAXREMAINS | --------------------- | 3900 | 

---

Subquery:

 SELECT MAX(remain) AS MaxRemain FROM item_new_company GROUP BY user 

with GROUP BY user and MAX(remain) , will give you the maximum remain for each user , then in the external query, SUM will give you the total.

---

Update

For SQL Server, the previous query should work fine, but there is another way:

 WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY [user] ORDER BY id DESC) AS rownum FROM item_new_company ) SELECT SUM(remain) AS Total FROM CTE WHERE rownum = 1; 

SQL Fiddle Demo