SQL SUM错误(求和和乘法)

Question

我有两个类似的表:

TABLE1
PC   Date       Num  Name   ID
8901 01/03/2013 5099 AAA    11
8901 01/03/2013 5099 AAA    11
8901 01/03/2013 5099 AAA    11
8901 02/03/2013 5099 AAA    12
8902 03/03/2013 5099 AAA    13
8903 04/03/2013 5099 AAA    14
TABLE2
PC   Date       Num  Name ID Bar2
8901 01/03/2013 5099 AAA  11 3
8901 01/03/2013 5099 AAA  11 2
8901 01/03/2013 5099 AAA  11 9
8901 02/03/2013 5099 AAA  12 3
8902 03/03/2013 5099 AAA  13 4
8903 04/03/2013 5099 AAA  14 5

和这个查询:

SELECT TABLE1.PC, TABLE1.Date, TABLE1.Num, TABLE1.Name, COUNT(*), SUM(TABLE2.Bar2)          
FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID         
GROUP BY TABLE1.PC, TABLE1.Date, TABLE1.Num, TABLE1.Name        
ORDER BY TABLE1.PC, TABLE1.Date 

结果是:

PC      Date            Num     Name COUNT  SUM
8901    01/03/2013 0:00 5099    AAA  9      42
8901    02/03/2013 0:00 5099    AAA  1      3
8902    03/03/2013 0:00 5099    AAA  1      4
8903    04/03/2013 0:00 5099    AAA  1      5

所以，它求和，然后乘以条目的n度((3+2+9)*3 = 42)

我做错了什么?谢谢! !

Answer 1

您想要的结果并不完全清楚，但是您可以使用子查询来获得Total Sum:

SELECT t1.PC, 
  t1.Date, 
  t1.Num, 
  t1.Name, 
  COUNT(*), 
  t2.Total
FROM TABLE1 t1
INNER JOIN
(
  SELECT SUM(Bar2) Total, ID
  FROM TABLE2 
  GROUP BY ID
) t2
  ON t1.ID = t2.ID
GROUP BY t1.PC, t1.Date, t1.Num, t1.Name, t2.Total
ORDER BY t1.PC, t1.Date

参见SQL Fiddle with Demo