0

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.

cnu
  • 815
  • 10
  • 22

2 Answers2

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
Matteo Tassinari
  • 18,121
  • 8
  • 60
  • 81
  • You shouldn't select columns that aren't being grouped by. Your results can be inconsistant. This answer goes into a little more detail: http://stackoverflow.com/a/1023435/1056965 – Steven Sep 12 '12 at 14:30
  • 1
    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. – Matteo Tassinari Sep 12 '12 at 14:35
  • 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. – Steven Sep 12 '12 at 14:38
  • You were right pointing it out, since I forgot to mention it in my answer, which I have now updated =) – Matteo Tassinari Sep 12 '12 at 14:39
  • 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. – Kermit Sep 12 '12 at 14:46
  • Now it should adhere the standards. – Matteo Tassinari Sep 12 '12 at 15:04
  • it has a problem, when i have groups with no images, its not showing all such groups – cnu Sep 12 '12 at 15:09
  • Try it now, I have updated it with a `LEFT JOIN` to handle empty groups. – Matteo Tassinari Sep 12 '12 at 15:12
  • Yes, now its working perfectly and accepting answer Thanks a lot for your efforts – cnu Sep 12 '12 at 15:14
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

cnu
  • 815
  • 10
  • 22