MySQL count number of times a result appears after grouping-CodePudding

I have a very similar problem to this one: Count unique occurrences per each distinct variable in MySQL but with a twist.

I'm working with a select statement that shows results much like what the above-mentioned question does.

If I boil down my statement into a simple form for this question my code looks like this:

SELECT
    salesman,
    brand
FROM
    sales
WHERE
    sales.date=something
GROUP BY salesman, brand

Resulting in an example output like this:

salesman brand
____ _______
Mark aaa
Mark bbb
Mark ccc
Mark ddd
Jane aaa
Jane bbb
Cody aaa

Without the GROUP BY part the results might look like this:

salesman brand
____ _______
Mark ddd
Jane bbb    
Mark aaa
Cody aaa
Mark ddd
Jane aaa
Mark aaa
Jane aaa
Mark ccc
Mark bbb

The twist I have is that I want to count the number of times each brand appears after the grouping and output it in the same table of results.

EG:

salesman brand brand_count
____ _______ _____
Mark aaa 3
Mark bbb 2
Mark ccc 1
Mark ddd 1
Jane aaa 3
Jane bbb 2
Cody aaa 3

I've tried adding count(*) to the SELECT but that only returns the number 2 for salesman Mark with brand aaa and 1 for Cody, which isn't what I'm after.

I want to show for each brand the number of times it appeared in the results. So brand aaa for example shows 3 times.

I suspect there might be a subquery needed though I'm not sure how it would work.

Any suggestions would be greatly appreciated.

EDIT: Original MySQL code below in case it helps

select
    ARM.SALESMAN AS 'SalesmanNumber',
    INM.BRAND AS 'Brand'
from
    ARMASTER ARM
    LEFT JOIN ARTRAN ART ON ARM.NUMBER = ART.CUST_NO
    LEFT JOIN INTRAN INTR ON ART.REF = INTR.REF
    LEFT JOIN ARSALECD SALESMAN ON ARM.SALESMAN = SALESMAN.CODE
    LEFT JOIN INMASTER INM ON INTR.STOCK_CODE = INM.CODE
where
    ARM.AREA = 01
    AND ARM.CUSTTYPE <> '99'
    AND ARM.SALESMAN NOT IN (24,48,49,50,51,52,71,72,74,90)
    AND (
        (YEAR(INTR.DATE) = YEAR(@mth) AND MONTH(INTR.DATE) = MONTH(@mth))
        OR (YEAR(DATE_ADD(@mth, INTERVAL -1 MONTH)) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -1 MONTH)))
        OR (YEAR(DATE_ADD(@mth, INTERVAL -2 MONTH)) AND MONTH(INTR.DATE) = MONTH(DATE_ADD(@mth, INTERVAL -2 MONTH)))
        )
group by Brand , SalesmanNumber;

I tried making the select part of the statement look like this per Robo suggestion:

    ARM.SALESMAN AS 'SalesmanNumber',
    INM.BRAND AS 'Brand',
    (
    SELECT
        count(*)
    FROM
        INMASTER AS s
    WHERE
        s.BRAND=INMASTER.BRAND
    ) AS brand_count

But get this error

Error Code: 1054. Unknown column 'INMASTER.BRAND' in 'where clause'

Also, I'm working with an old database version: MySQL 5.1.60

CodePudding user response：

You need to use a subquery:

SELECT
    salesman,
    brand,
    (
        SELECT
            count(*)
        FROM
            sales AS s
        WHERE
            s.brand=sales.brand
    ) AS brand_count
FROM
    sales
WHERE
    sales.date=something
GROUP BY salesman, brand

CodePudding user response：

On an older MySQL version you can use:

select s.salesman,s.brand,brand_count
from sales s
inner join (select brand,count(brand) as brand_count
            from ( select salesman,brand
                   from sales 
                   group by salesman,brand
                 ) as tbl
            group by brand     
           ) as x on x.brand=s.brand
group by salesman,brand,brand_count
order by s.salesman  ;

https://dbfiddle.uk/EevII2mZ

Group by the result , in an outer query count the brand and then use that as a subquery to join with the primary same table

CodePudding user response：

On MySQL v8 , you can use COUNT() OVER () function.

Create table & data example:

CREATE TABLE sales(
  salesman VARCHAR(255),
  brand VARCHAR(255),
  date DATE);

INSERT INTO sales VALUES
('Mark','ddd','2022-10-06'),
('Jane','bbb','2022-10-06'),
('Mark','aaa','2022-10-06'),
('Cody','aaa','2022-10-06'),
('Mark','ddd','2022-10-06'),
('Jane','aaa','2022-10-06'),
('Mark','aaa','2022-10-06'),
('Jane','aaa','2022-10-06'),
('Mark','ccc','2022-10-06'),
('Mark','bbb','2022-10-06');

Query:

SELECT
    salesman,
    brand,
    COUNT(brand) OVER (PARTITION BY Brand) AS brand_count
FROM
    sales
WHERE date='2022-10-06'
GROUP BY salesman, brand
ORDER BY salesman DESC, brand

Results:

salesman	brand	brand_count
Mark	aaa	3
Mark	bbb	2
Mark	ccc	1
Mark	ddd	1
Jane	aaa	3
Jane	bbb	2
Cody	aaa	3

Demo fiddle

For older MySQL version:

SELECT t1.salesman, t1.brand, brand_count
  FROM sales t1
  JOIN

/*this part of the query is just to get the brand_count*/
(SELECT brand, COUNT(*) AS brand_count
  FROM
(SELECT salesman, brand
FROM sales
WHERE date='2022-10-06'
GROUP BY salesman, brand) t
  GROUP BY brand) t2
/*this part of the query is just to get the brand_count*/

ON t1.brand=t2.brand
  WHERE t1.date='2022-10-06'
GROUP BY t1.salesman, t1.brand
  ORDER BY t1.salesman DESC, t1.brand;

Demo fiddle