mysql - SQL:有条件 SUM

Question

我构建了一个查询来比较一家公司与其他公司的销售统计数据。

简化的，就像:

SELECT SUM(CASE WHEN company=foobar THEN sales ELSE 0) as sales_foobar,
SUM(sales) as sales_total, country, year, product FROM sales_table
GROUP BY country, year, product HAVING (the above sum again) > 0

合规性规则要求只能显示公司有销售的行，虽然我的代码会丢弃 foobar 没有销售的行，但由于订单和退款相等，它也会删除销售额为 0 的行。

现在，我怎样才能只获取公司有销售数据的行？

Answer 1

您可以在 HAVING 子句中计算与公司匹配的行数:

SELECT SUM(CASE WHEN company = foobar THEN sales ELSE 0 END) as sales_foobar,
       SUM(sales) as sales_total, country, year, product
FROM sales_table
GROUP BY country, year, product
HAVING SUM( company = foobar ) > 0;  -- there is at least one record for the company