Как найти максимальную сумму sql

Есть таблица, как вывести максимальную сумму по какому-либо параметру? СУБД mySQL

Например такая таблица

|id|par|  
|1 | 2 |
|1 | 6 |
|2 | 2 |
|2 | 1 |

Получить такой id, у которого сумма всех par максимальна.

задан 30 ноя 2013 в 11:00

Стандартное решение, которое даст правильный результат, если максимум достигается для нескольких id:

select id, sum(par) as par from t group by id 
having sum(par) >= ALL(
select sum(par) from t group by id
);

ответ дан 30 ноя 2013 в 16:59

select id, s 
 from ( select id, sum(pair) as s  
          from t group by id
        order by 2 desc) 
limit 1

ответ дан 30 ноя 2013 в 11:28

select id, s 
 from ( select id, sum(pair) as s  
          from t group by id
        order by 2 desc) 
limit 1

→ Ссылка

Автор решения: msi

select id, sum(par) as par from t group by id 
having sum(par) >= ALL(
select sum(par) from t group by id
);

→ Ссылка

MYSQL

SELECT `DIV`, PROD, CUST,QTY from 
(
SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD
) AS T
WHERE QTY=(SELECT MAX(QTY) FROM (SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD) AS T)

ИЛИ ЖЕ

SELECT `DIV`, PROD, CUST,QTY from 
(
SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD
) AS T ORDER BY QTY DESC LIMIT 1;

Live Demo

http://sqlfiddle.com/

SQL SERVER

Использование CTE

WITH CTE AS
(
 SELECT DIV, PROD, CUST, SUM(QTY) as QTY,
 DENSE_RANK() OVER (PARTITION BY DIV, PROD ORDER BY SUM(QTY) DESC) AS Rank
 FROM 
 table1 
 GROUP BY DIV, PROD, CUST
)
SELECT DIV, PROD, CUST,QTY FROM CTE
WHERE Rank=1
ORDER BY DIV, PROD

Использование вложенного запроса

SELECT DIV, 
       PROD, 
       CUST,
       QTY 
FROM ( 
      SELECT DIV, PROD, CUST, SUM(QTY) as QTY, 
      DENSE_RANK() OVER (PARTITION BY DIV, PROD ORDER BY SUM(QTY) DESC) AS Rank
      FROM table1 
      GROUP BY DIV, PROD, CUST )AS T1 
 WHERE Rank=1 ORDER BY DIV, PROD;

Live Demo

http://sqlfiddle.com/

Вы можете отфильтровать вашу совокупность sum(qty) с помощью having пункт

select 
       DIV, PROD, CUST, SUM(QTY) as QTY 
from table
group by DIV, PROD, CUST
having sum(QTY) = ( select max(QTY) from (
                    select sum(QTY) as QTY from table
                    group by DIV, PROD, CUST)a)

Попробуйте это в MySQL:

SELECT A.`DIV`, A.PROD, IF(A.`DIV`=0,'NAN',A.AGG_QTY/A.`DIV`) QTY, A.CUST
FROM (SELECT `DIV`, PROD, CUST, SUM(QTY) AGG_QTY
      FROM `TABLE` 
      GROUP BY `DIV`, PROD, CUST
      ORDER BY SUM(QTY) DESC
      LIMIT 1) A;

Посмотрите, как он работает на SQL Fiddle

private void button2_Click(object sender, EventArgs e)
{
    string selectQuery = "SELECT `username`, `password` FROM `users` WHERE `username` = '"+ username.Text + "' AND `password` = '" + password.Text +"';";
    MySqlDataAdapter adapter = new MySqlDataAdapter(selectQuery, DBConnect);
    DataTable table = new DataTable();
    adapter.Fill(table);
    if (table.Rows.Count <= 0)
    {
        // label_Message.ForeColor = Color.Red;
        // label_Message.Text = "Username Or Password Are Invalid";
        //timer1.Start();
        MessageBox.Show("Username Or Password Are Invalid");
    }
    else
    {
        //  panel1.Height = 0;
        //  label_Message.ForeColor = Color.Green;
        //  label_Message.Text = "Login Successfully";
        //   timer1.Start();
        MessageBox.Show("Login Successfully");
        admin1 admin1 = new admin1();
        admin1.Show();
        this.Hide();
    }
    table.Clear();
}