MySQL: Aggregating counts

Question

I'm trying to find how many companies had sales in a specific segment. I've managed to get a count of the sales entries (5), but I can't seem to aggregate by the product segment as well. Please see this simplification:

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
;

The goal is to get "companies" to show 3, as there are three companies that had sales in segment A.

Mureinik · Accepted Answer · 2017-05-08 08:06:53Z

1

You could use the distinct modifier in the count function to get the number of different entries:

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

answered May 8, 2017 at 8:06

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pkg Over a year ago

D'oh, it always seems so easy when someone is able to accurately answer it. Great, thanks!

Collectives™ on Stack Overflow

MySQL: Aggregating counts

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related