正确使用MySQL的总和功能

Question

我有如下表：

Field   | Type     | Null | Key | Default | Extra | 
+-----------------+----------------------+------+-----+---------+-------+ 
| SMILES   | char(200)   | NO | PRI |   |  | 
| ConfRank  | smallint(5) unsigned | NO |  | NULL |  | 
| CompTime  | double    | YES |  | NULL |  | 

我想要得到一个特定的SMILES（意COMPTIME添加特定的笑容人confrank）总COMPTIME。

我试过如下：

SELECT SMILES,(SUM(CompTime)) From GeoAndEnergies GROUP BY ConfRank WHERE SMILES='C'; 

而且收到此错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' WHERE SMILES='C' ' at line 1

我也试过：

SELECT SMILES,(SUM(CompTime)) From GeoAndEnergies GROUP BY ConfRank WHERE SMILES='C' GROUPBY ConfRank ; 

并得到了另一个错误：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' WHERE SMILES='C' GROUPBY ConfRank ' at line 1

什么是正确的语法？

Answer 1

尝试以下

SELECT SMILES,(SUM(CompTime)) From GeoAndEnergies WHERE SMILES='C' GROUP BY ConfRank; 

Answer 2

您的GROUP BY应该遵循WHERE条款。更新内容如下：后where条款

SELECT `SMILES`, 
     SUM(`CompTime`) 
    FROM `GeoAndEnergies` 
    WHERE `SMILES` = 'C' 
GROUP BY `ConfRank`; 

Answer 3

你group by条款应该会出现。

Answer 4

所以我用 SELECT SMILES，（SUM（COMPTIME））从GeoAndEnergies其中，方法= 'PM6' GROUP BY SMILES INTO OUTFILE“的/ var/lib中/ MySQL的，文件/ data_PM6_timesum“;
and mysql> SELECT SMILES，（SUM（CompTime））From GeoAndEnergies WHERE Method ='PM6'GROUP BY SMILES INTO OUTFILE“/ var/lib/mysql-files/data_PM6_time”; 和SELECT SMILES，NRotBond，NHeavyAtom From GeoAndEnergies WHERE Method ='PM6'AND ConfRank = 1 INTO OUTFILE“/ var/lib/mysql-files // data_PM6_rotheavy”; 创建data_PM6_rotheavy和data_PM6_time。 通过捕获2你应该有你想要的。