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
You need to use a subquery:
On MySQL v8+, you can use
COUNT() OVER ()
function.Create table & data example:
Query:
Results:
Demo fiddle
For older MySQL version:
Demo fiddle
On an older MySQL version you can use:
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