mysql - MySQL:汇总计数

我想知道有多少公司在一个特定的细分市场有销售。我已经设法统计了销售条目（5），但似乎也不能按产品细分汇总。请看这个简化：
http://sqlfiddle.com/#!9/685cb/1

CREATE TABLE Table1
    (`company` text, `sales` int, `segment` text)
;

INSERT INTO Table1
    (`company`, `segment`, `sales`)
VALUES
    ('ACME',10,100),
    ('ACME',11,100),
    ('HAL',10,25),
    ('HAL',13,25),
    ('GEN',11,50)
;

SELECT COUNT(company) AS companies,
CASE
    WHEN segment IN (10, 11, 12, 13, 14, 15, 16)
    THEN 'Product segment A'
    WHEN segment IN (20, 21, 22)
    THEN 'Product segment B'
    WHEN segment IN (30)
    THEN 'Product segment C'
    END AS grp, SUM(sales) AS sum_sales
FROM Table1
WHERE
       (company LIKE '%ACME%'
     OR company LIKE '%HAL%'
     OR company LIKE '%GEN%'
    )
AND
    segment IN (10, 11, 12, 13, 14, 15 ,16, 20, 21, 22, 30)
GROUP BY grp
ORDER BY grp
;

目标是让“公司”展示3家公司，因为A区有3家公司有销售。

最佳答案

您可以使用distinct函数中的count修饰符来获取不同条目数：

SELECT COUNT(DISTINCT company) AS companies,
-- Here -----^
CASE
    WHEN segment IN (10, 11, 12, 13, 14, 15, 16)
    THEN 'Product segment A'
    WHEN segment IN (20, 21, 22)
    THEN 'Product segment B'
    WHEN segment IN (30)
    THEN 'Product segment C'
    END AS grp, SUM(sales) AS sum_sales
FROM Table1
WHERE
       (company LIKE '%ACME%'
     OR company LIKE '%HAL%'
     OR company LIKE '%GEN%'
    )
AND
    segment IN (10, 11, 12, 13, 14, 15 ,16, 20, 21, 22, 30)
GROUP BY grp
ORDER BY grp
;

SQLFiddle

关于mysql - MySQL:汇总计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/43842615/