php mysql join and count

Question

I have two tables imagesgroup and images

imagesgroup has following fields id and group_name

images has following fields id ,image_name and category in which we store imagesgroup.id

Now i want to show all image groups along with number of images for each image group.

i.e i want to display something like this

group_name | no. of items in that group

home |10

Office | 5

Party | 0

please help me out thanks a ton in advance.

Matteo Tassinari · Accepted Answer · 2012-09-12 15:12:00Z

1

Try this:

SELECT group_name, COUNT(DISTINCT i.id)
FROM imagesgroup ig
LEFT JOIN images i ON (ig.id = i.category)
GROUP BY ig.id, ig.group_name

edited Sep 12, 2012 at 15:12

answered Sep 12, 2012 at 14:20

Matteo Tassinari

18.6k8 gold badges65 silver badges85 bronze badges

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

9 Comments

Steven Over a year ago

You shouldn't select columns that aren't being grouped by. Your results can be inconsistant. This answer goes into a little more detail: stackoverflow.com/a/1023435/1056965

Matteo Tassinari Over a year ago

In this case it shouldn't be. I know it can be inconsistant, but the group_name is the same for each possible row the query gets grouped by, so there should be no inconsistencies, in fact it follows what is suggested in the accepted answer of the question you linked.

Steven Over a year ago

Glad to hear you're aware. I figured I'd pass the knowledge on just in case. I didn't know until it ended up causing issues for me.

Matteo Tassinari Over a year ago

You were right pointing it out, since I forgot to mention it in my answer, which I have now updated =)

Kermit Over a year ago

This is poor, if not terrible, practice. Just because MySQL allows you to select columns that are not in the aggregate does not make it correct.

|

cnu · Accepted Answer · 2012-09-12 15:21:53Z

0

Thanks for all answers

This is working for me

SELECT l.group_name ,l.id, COUNT(ld.category) as COUNT FROM imagesgroup AS l LEFT OUTER JOIN images AS ld ON ld.category = l.id GROUP BY l.id ORDER BY l.id

Above accepted solution is also working perfectly

edited Sep 12, 2012 at 15:21

answered Sep 12, 2012 at 15:12

cnu

81510 silver badges23 bronze badges

Collectives™ on Stack Overflow

php mysql join and count

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related