MySQL COUNT of Grouped rows

Question

I have following table 'tbl_pads' with one column named 'id_pad' with 6 rows:

pad_1 |
pad_1 |
pad_2 |
pad_3 |
pad_3 |
pad_3 |

With SELECT id_pad FROM tbl_pads GROUP BY id_pad I get following rows:

pad_1 |
pad_2 |
pad_3 |

With SELECT COUNT(id_pad) AS COUNT_PADS FROM tbl_pads GROUP BY id_pad I get following rows

2 | 
1 | 
3 |

How can get the rows Count of the Grouped Statement? I expect the result 3

@DanielW. I think you misunderstood the question. – Barmar Aug 10 '20 at 15:29 — Barmar, Aug 10 '20 at 15:29
@LikiCrus That was posted as an answer a while ago. – Barmar Aug 10 '20 at 15:35 — Barmar, Aug 10 '20 at 15:35

score 4 · Answer 1 · answered Aug 10 '20 at 15:23

4

Another was of phrasing the question is that you want to get the number of different id_pad values. Once you phrase it like that, it becomes clearer that using a distinct count will work:

SELECT COUNT(DISTINCT id_pad) FROM tbl_pads

answered Aug 10 '20 at 15:23

Mureinik

297,002
52
306
350

nbk · Answer 2 · 2020-08-10T15:30:07.717

0

If you want the number of individual id_pads.

CREATE TABLE tbl_pads
    (`id_pad` varchar(5))
;
    
INSERT INTO tbl_pads
    (`id_pad`)
VALUES
('pad_1'),
    ('pad_1'),
    ('pad_2'),
    ('pad_3'),
    ('pad_3'),
    ('pad_3')
;

SELECT COUNT(id_pad) AS COUNT_PADS FROM tbl_pads WHERE id_pad = 'pad_3'

| COUNT_PADS |
| ---------: |
|          3 |

db<>fiddle here

edited Aug 10 '20 at 15:30

answered Aug 10 '20 at 15:25

nbk

45,398
8
30
47

I think you misunderstood the question. – Barmar Aug 10 '20 at 15:27
@Barmar you are absolutely right – nbk Aug 10 '20 at 15:30

Vivek Jain · Accepted Answer · 2020-08-10T17:53:32.117

If you want to rows count of group statement in single row then you can use below query.

SELECT COUNT(id_pad) over() AS COUNT_PADS FROM tbl_pads group by id_pad limit 1;

OR

SELECT COUNT(DISTINCT id_pad) FROM tbl_pads;

And if you want multiple row based on group statement and rows count along with this then you can go with this.

SELECT COUNT(id_pad) over(), id_pad AS COUNT_PADS FROM tbl_pads group by id_pad;

See the db<>fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=97021f2b9a1cb360c1258a2bfb4e072a

MySQL COUNT of Grouped rows

3 Answers3