MySQL -SUM(分组)？

Question

我遇到了一个问题，我需要在select语句中得到一组值的和。基本上，我需要：

  SELECT col1, col2, SUM(col2) AS col3 
    FROM myTable 
GROUP BY col1

在这里，col1将分成大约3个组，而col2包含大约40个值.因此，我不需要col3是相同的值，col2的和，而是col2对每个col1组的和

*编辑 * ok -以下是实际查询：

select a.id,a.alloyname,a.label,a.symbol, g.grade, 
    if(a.id = 1,(
        ((( select avg(cost/2204.6) as averageCost 
            from nas_cost where cost != '0' 
            and `date` >= '#variables.firstOfMonth#' 
            and `date` <= '#variables.lastOfMonth#') - t.value) * (astm.astm/100) * 1.2)),
        if(a.id = 2,(
            ((ep.estPrice - t.value) * (astm.astm/100) * 1.2)),
        if(a.id = 3 or a.id = 4 or a.id = 6 or a.id = 7,(
            ((ep.estPrice - t.value) * (astm.astm/100) * 0.012)),
        if(a.id = 5,(
            ((ep.estPrice - t.value)/2240 * (astm.astm/100))),
        if(a.id = 8,(
            if(((ep.estPrice - t.value)* (astm.astm/100)) >= 0,((ep.estPrice - t.value)* (astm.astm/100)), 0)),
    0)))))
    as thisValue

from nas_alloys a 
left join nas_triggers t on t.alloyid = a.id
left join nas_astm astm on astm.alloyid = a.id
left join nas_estimatedprice ep on ep.alloyid = a.id
left join nas_grades g on g.id = astm.gradeid
order by g.grade;

我需要按等级分组的“thisValue”的总数.试着把我的头绕过去..。

Answer 1

ANSI-92格式：

   SELECT a.col1, 
          a.col2, 
          COALESCE(b.col3, 0) AS col3
     FROM YOUR_TABLE a
LEFT JOIN (SELECT t.col1,
                  SUM(t.col2) AS col3
             FROM YOUR_TABLE t
         GROUP BY t.col1) b ON b.col1 = a.col1

当没有支持的col1引用时，左侧联接将返回NULL --在本例中，合并将将这些情况转换为零

ANSI-89格式

SELECT a.col1, 
       a.col2, 
       (SELECT SUM(b.col2)
                   FROM YOUR_TABLE b
                  WHERE b.col1 = a.col1) AS col3
  FROM YOUR_TABLE a

当子查询中没有支持的col1引用时，这将返回NULL。

Answer 2

您可以为此使用子查询。

SELECT col1, col2, (
    select SUM(col2)
    from mytable A
    where A.col1 = myTable.col1) as col3
FROM myTable