CODEWITHSUNDEEP
mysqldoctrine-ormgroup-by
Akuma
Akuma

Reputation: 583

How to get number of rows in group after GROUP BY select in MySQL?

Let MySQL table example

id | grp | title
1  | 1   | Item1
2  | 1   | Item2
3  | 2   | Item3

After sql SELECT * FROM table GROUP BY grp we have result:

id | grp | title
1  | 1   | Item1
3  | 2   | Item3

How can I get number of rows in group? Like this:

id | grp | title | grp_count
1  | 1   | Item1 | 2
3  | 2   | Item3 | 1

And if it possible, I want to do it by Doctrine2

Upvotes: 0

Views: 67

Answers (3)

Rakesh K
Rakesh K

Reputation: 712

You can try this 

SELECT COUNT(*) 
FROM (SELECT *
FROM table
GROUP BY column_name);

Upvotes: -1

user3522371
user3522371

Reputation: 

SELECT *, COUNT(*) titlesNumber
FROM myTable
GROUP BY grp

DEMO.

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If selecting the id and title does not matter in other words it will select any of id and title while doing group by you can use the following

select
id,
grp,
title,
count(*) as  grp_count
from table
GROUP BY grp

Upvotes: 2

Related Questions