mysql - multiple rows of COUNT query

Question

For example I have a data here

ID    Doctor    Status
1     Wooka     Approved
2     Jamba     Approved
3     Jamba     Approved
4     Wooka     Approved
5     Wooka     Approved
6     Wooka     Approved
7     Wooka     Approved
.    .          .
.    .          .
.    .          .
99

For the first 7 rows (demonstration purposes, not limited to 7) I want it to output like this

Doctor    Count
Wooka     5
Jamba     2

Although I am not sure how to do that in COUNT()

SELECT COUNT(*) FROM table WHERE status='Approved'

I really don't have any idea how to make it flexible as to what I've asked.

Look up how to GROUP BY

P.Salmon
– P.Salmon

2016-07-20 16:36:16 +00:00
Commented Jul 20, 2016 at 16:36 — P.Salmon
– P.Salmon, Commented Jul 20, 2016 at 16:36

Simone · Accepted Answer · 2016-07-20 16:41:16Z

2

SELECT
  t.doctor,
  COUNT(*) AS c
FROM
  table t
WHERE
  t.status = 'Approved'
GROUP BY
  t.doctor

answered Jul 20, 2016 at 16:41

Simone

664 bronze badges

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

1 Comment

Mureinik Over a year ago

This doesn't handle the "first 7 rows" requirement.

Mureinik · Accepted Answer · 2016-07-20 16:40:01Z

0

You could use a limit clause in a subquery to limit the number of rows returned, and then wrap this query with an aggregate query that performs a count:

SELECT   doctor, COUNT(*)
FROM     (SELECT   doctor
          FROM     mytable
          WHERE    status = 'Approved'
          ORDER BY id ASC
          LIMIT    7 -- or some other number
         ) t
GROUP BY doctor

answered Jul 20, 2016 at 16:40

Mureinik

316k54 gold badges400 silver badges405 bronze badges

4 Comments

Mureinik Over a year ago

@Noobster yes. That's what the group by clause is there for

Noobster Over a year ago

Hello, yes this works. But im wondering how if to map all the status? Like Approved, Pending, Denied, ...the list goes on and on?

Mureinik Over a year ago

@Noobster If you want a count per status, drop the inner where clause and add the status to the group by clause.

Mureinik Over a year ago

@Noobster yes, indeed.

Collectives™ on Stack Overflow

mysql - multiple rows of COUNT query

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related