mysql - 合并和添加相似的结果

Question

当检索到多个类似记录时，我到处搜索，试图找到我需要加在一起的 1 个字段。我的查询和结果如下。

这是我的查询:

SELECT m.id as 'id', m.name as 'Merchant Name', c.numStamps as 'Num Stampts on Card', COUNT(c.numStamps) as '# Users with this # Stampts'
FROM Merchants m, Cards c, Stores s
WHERE c.store_id = s.id
AND s.merchant_id = m.id
and c.redeemed = 0
and c.Numstamps>8
and c.datelaststamp > '2011-07-16 07:00:00'
group by m.id, c.numStamps
order by m.name, c.numStamps

结果如下:

id Merchant Name Stampts UserStampts

391 360 Gourmet Burritos 9 3
391 360 Gourmet Burritos 10 6

我需要组合 id 和 Merchant Name。 Stampts 并不重要，但我需要将 UserStampts 加在一起。所以我需要的返回是:

391 360 Gourmet Burritos 10 9

Answer 1

修改查询的一种简单方法是将其变成嵌套查询:

SELECT id, Merchant, SUM(Stampts) AS Stampts, SUM(UserStampts) AS UserStampts
FROM ( ... your old query goes here ... ) AS oldquery
GROUP BY id, Merchant;

这将对两列(独立地)求和。