Aggregating a simple COUNT in MySQL

Question

I am formulating a MySQL query that tries to aggregate a COUNT. The data looks like this....table is "workers"

   NAME      DPT
   -----     ----
   CLARK     10
   JAMES     10
   ALLEN     10
   ADAMS     20
   WU        20
   MARTIN    30
   FITZ      30
   SCHMIDT   30
   MILLER    30

I need to list the table like this with a count in the departments

   NAME      DPT   CNT
   -----     ----   ---
   CLARK     10     3
   JAMES     10     3
   ALLEN     10     3
   ADAMS     20     2
   WU        20     2
   MARTIN    30     4
   FITZ      30     4
   SCHMIDT   30     4
   MILLER    30     4

I tried this...

SELECT DISTINCT(NAME), DPT, COUNT(DPT) as CNT FROM workers GROUP BY DPT;

with no success. Pretty sure it's something simple but I'm stumped. Any ideas on where I am going wrong? - JW

cjungel · Accepted Answer · 2018-06-21 15:34:20Z

2

Since the count aggregation is on the DPT field only you need to have a subquery for that and then join with the workers table to get the names.

SELECT w.NAME, w.DPT, tot.CNT
FROM workers AS w JOIN (SELECT DPT, COUNT(1) AS CNT FROM workers GROUP BY DPT) AS tot ON w.DPT = tot.DPT 
ORDER BY w.DPT, w.NAME;

edited Jun 21, 2018 at 15:34

answered Jun 21, 2018 at 0:16

cjungel

3,7911 gold badge27 silver badges20 bronze badges

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

2 Comments

Devon Bessemer Over a year ago

+1, This join would be faster than my subquery, but I'd recommend using an explicit join to make it more readable.

cjungel Over a year ago

Thanks @Devon, changed it to use an explicit JOIN as you suggested.

Devon Bessemer · Accepted Answer · 2018-06-21 00:15:57Z

0

When you group, you aggregate all the rows under that field. Therefore, you'll only get rows for each distinct value of DPT.

You could use a subquery to get the count for the same DPT value.

SELECT NAME, DPT, (SELECT COUNT(*) FROM workers w2 WHERE w2.DPT = workers.DPT) as CNT
FROM workers;

answered Jun 21, 2018 at 0:15

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2018-06-21 03:55:35Z

0

You could use windowed COUNT(MySQL 8.0 and above):

SELECT *, COUNT(*) OVER(PARTITION BY dpt) AS cnt
FROM workers;

DBFiddle Demo

answered Jun 21, 2018 at 3:55

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

Aggregating a simple COUNT in MySQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related