skip to Main Content

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

3

Answers


  1. 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
    
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search