MySQL 使用 Group By 多列求和

Question

以下查询:

SELECT `brand`,
       count(*) * `denomination` AS 'total'
FROM `inventory`
WHERE `owner` = 'owner-one'
    AND `currency` = 'GBP'
    AND `activated_at` IS NULL
    AND `expires_at` >= '2018-06-21'
GROUP BY `owner`,
         `brand`,
         `currency`,
         `denomination`

产品:

| brand     | total |
|-----------|-------|
| brand-one | 3000  |
| brand-one | 8000  |
| brand-two | 10000 |

我想要的是进一步按项目分组并对总数进行求和，以便输出如下:

| brand     | total |
|-----------|-------|
| brand-one | 11000 |
| brand-two | 10000 |

数据:

Answer 1

您得到 3 条记录的原因是分组依据包含面额。如果我们改变逻辑，用求和代替 count(*) * 面额，那么我们就可以获得后面的两条记录。 (前提是您从群组中删除面额)...但也许我遗漏了一些东西。

SELECT `brand`,
       Sum(`denomination`) AS 'total'
FROM `inventory`
WHERE `owner` = 'owner-one'
  AND `currency` = 'GBP'
  AND `activated_at` IS NULL
  AND `expires_at` >= '2018-06-21'
GROUP BY `owner`,
         `brand`,
         `currency`